SQL Server 2014 :: UNION Vs PIVOT For De-flattening SOME Columns Of A Partially Flat Table

Jan 23, 2015

We have a table with 500+ columns. The data is non-normalized, i.e. there are four groups of fields for for "people", followed by data that applies to all people in the row (a "household").For ad-hoc queries, and because I wanted to index columns within each person (person 1's age, person 2's age, etc.), I used UNION:

SELECT P1Firstname AS FirstName, P1LastName as LastName, P1birthday AS birthday, HouseholdIncome, HouseholdNumber of Children, <other "household" columns>
UNION
SELECT P2Firstname AS FirstName, P2LastName as LastName, P2birthday AS birthday, HouseholdIncome, HouseholdNumber of Children, <other "household" columns>

I could get at least the P1... P2... P3... columns with PIVOT, but then I believe I'd have to JOIN back to the row anyway for the "household" columns. Performance of UNION good, but another person here chose to use PIVOT instead.I can' find any articles on PIVOT vs. UNION for "de-flattening".

View 2 Replies


ADVERTISEMENT

SQL Server 2014 :: Pivot IN Clause - Dynamic Columns

May 12, 2015

The first select is running fine but due to extra values added to the table the list of manual difined columns must be added manualy each time new values occur.

Is it possible to make the PIVOT's IN clause dynamicly as stated in the second script (it is based on the same table #source) when running it prompts the next error;

Msg 156, Level 15, State 1, Line 315
Incorrect syntax near the keyword 'select'.
Msg 102, Level 15, State 1, Line 315
Incorrect syntax near ')'.

adding or moving ')' or '(' are not working.......

select *
into #temp
from #source
pivot ( avg(value) for drive in ([C], [D], [E], [F], [G], [H], [T], [U], [V] )) as value
select * from #temp order by .........

versus

select *
into #temp
from #source
pivot ( avg(value) for drive in (select distinct(column) from #source)) as value

select * from #temp order by .....

View 3 Replies View Related

SQL Server 2014 :: UNION ALL In View With Temp Table

May 8, 2015

I have a performance issue with one of the views when I join the view with a temp table

I have 2 Views - View1 and View2.

There is a third view - view_UNION where the

view_UNION =
SELECT * FROM View1
UNION ALL
SELECT * FROM View2

If I have a query like -

Select view_UNION.* FROM
view_UNION INNER JOIN #TMP ON #TMP.ID = view_UNION.ID

the execution is too slow.

But if I execute the views separately, I get good performance.

How to improve the performance of the view_Union

View 7 Replies View Related

SQL Server 2014 :: Creating A Table With Updatable Columns And Read-only Columns

May 26, 2015

Here is My requirement, I'm not sure if this is possible. Creating table called master like col1, col2 col3, col4 , col5 ...Where Col1, col2 are updatable - this can be done easily

Col3, col4 are columns in another table but these can be just a read only ?? Is this possible ? this is possible with View but not friendly with share point CRUD...Col 5 is a computed column of col 2 and col5 ? if above step can be done then sure this can be done I guess.

View 4 Replies View Related

SQL Server 2014 :: Pivot Table With Column Names To Rows?

Aug 1, 2015

I have a table with following rows.

FY REVCODE Jul Jun
2015 BNQ 1054839 2000000
2015 FNB 89032 1000000
2015 RS 1067299 3000000

I am looking to convert it to

Month BNQ FNB RS
JUL 1054839 89032 1067299
JUN 2000000 1000000 3000000

I tried with the following and result is coming for one month i.e. JUL but not with the second Month i.e Jun

SELECT 'Jul1' AS MON, [BNQ], [FNB], [RS]
FROM
(SELECT REVENUECODE, SUM(ROUND(((Jul/31)*30),0)) AS JUL
FROM RM_USERBUDGETTBL
WHERE USERNAME='rahul' AND FY=2015
GROUP BY REVENUECODE, USERNAME
) AS SourceTable
PIVOT
(SUM(JUL) FOR REVENUECODE IN ([BNQ], [FNB], [RS])) AS PivotTable

Results:

MONTHBNQ FNB RS
Jul11054839 89032 1067299

View 4 Replies View Related

SQL Server 2014 :: Loop And Query CSV Files In Folder Using Union All Query To Form Resultant Table On Server?

Jun 27, 2014

I am trying to run a UNION ALL query in SQL SERVER 2014 on multiple large CSV files - the result of which i want to get into a table in SQL Server. below is the query which works in MSAccess but not on SQL Server 2014:

SELECT * INTO tbl_ALLCOMBINED FROM OPENROWSET
(
'Microsoft.JET.OLEDB.4.0' , 'Text;Database=D:DownloadsCSV;HDR=YES',
'SELECT t.*, (substring(t.[week],3,4))*1 as iYEAR,
''SPAIN'' as [sCOUNTRY], ''EURO'' as [sCHAR],

[Code] ....

What i need is:

1] to create the resultant tbl_ALLCOMBINED table

2] transform this table using PIVOT command with following transformation as shown below:

PAGEFIELD: set on Level = 'Item'
COLUMNFIELD: Sale_Week (showing 1 to 52 numbers for columns)
ROWFIELD: sCOUNTRY, sCHAR, CATEGORY, MANUFACTURER, BRAND, DESCRIPTION, EAN (in this order)
DATAFIELD: 'Sale Value with Innovation'

3] Can the transformed form show columnfields >255 columns i.e. if i want to show all KPI values in datafield?

P.S: the CSV's contain the same number of columns and datatype but the columns are >100, so i dont think it will be feasible to use a stored proc to create a table specifying that number of columns.

View 9 Replies View Related

SQL Server 2012 :: Dynamic Table Pivot With Multiple Columns

Jan 23, 2014

I am trying to pivot table DYNAMICALLY but couldn't get the desired result .

Here is the code to create a table

create table Report
(
deck char(3),
Jib_in float,
rev int,
rev_insight int,
jib_out float,

[Code] .....

Code written so far. this pivots the column deck and jib_in into rows but thats it only TWO ROWS i.e the one i put inside aggregate function under PIVOT function and one i put inside QUOTENAME()

DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX);
SET @columns = N'';
SELECT @columns += N', p.' + QUOTENAME(deck)
FROM (SELECT p.deck FROM dbo.report AS p
GROUP BY p.deck) AS x;

[Code] ....

I need all the columns to be pivoted and show on the pivoted table. I am very new at dynamic pivot. I tried so many ways to add other columns but no avail!!

View 1 Replies View Related

SQL Server 2012 :: Convert Rows To Columns Using Dynamic PIVOT Table

Feb 3, 2015

I have this query:

SELECT TOP (100) PERCENT dbo.Filteredfs_franchise.fs_franchiseid AS FranchiseId, dbo.Filteredfs_franchise.fs_brandidname AS Brand,
dbo.Filteredfs_franchise.fs_franchisetypename AS [Franchise Type], dbo.Filteredfs_franchise.fs_franchisenumber AS [Franchise Number],
dbo.Filteredfs_franchise.fs_transactiontypename AS [Transaction Type], dbo.Filteredfs_franchise.fs_franchisestatusname AS [Status Code],

[Code] ....

I need to pivot this so I can get one row per franchiseID and multiple columns for [Franchisee Name Entity] and [Franchise Name Individual]. Each [Franchisee Name Entity] and [Franchise Name Individual] has associated percentage of ownership.

This has to be dynamic, because each FranchiseID can have anywhere from 1 to 12 respective owners and those can be any combination of of Entity and Individual. Please, see the attached example for Franchise Number 129 (that one would have 6 additional columns because there are 3 Individual owners with 1 respective Percentage of ownership).

The question is how do I PIVOT and preserve the percentage of ownership?

View 3 Replies View Related

Pivot Table/Flat File - HELP!

May 16, 2008

Ok,I revamped my query, I thought I fa different way.....

Select
RecordID,
Type, (Traditional, Teclar, AllClaims)
Year,
Month,
TotalCharges,
AdjustedTotalSavings,
AverageClaim,
PercentSavings,
DHSIncome
From sumClaimsSettlement

What I need the end result to look like...

Type Q1 Q2 Q3 Q4

Traditional
Teclar
All Claims

Under the Quarter cloumn will be Total Charges. What is the better way to do this? Should I go with a Pivot table or make it into a flat file?

View 2 Replies View Related

SQL Server 2014 :: Columns List With And Without Identity Column In A Table?

Feb 24, 2015

I have the following 2 Query's - case when Table has no Identity Column and other with identity Column . I am planning to make it to single Query .

Query 1:
SELECT @ColumnNamesWhenNoIdentity = COALESCE(@ColumnNamesWhenNoIdentity + ',', '') + Name +'= SOURCE.'+Name
FROM sys.columns WITH(NOLOCK) WHERE object_id =
(
SELECT sys.objects.object_id
FROM sys.objects WITH(NOLOCK)
INNER JOIN sys.schemas WITH(NOLOCK) ON sys.objects.schema_id = sys.schemas.schema_id
WHERE sys.objects.TYPE = 'U' AND sys.objects.Name = 'Testing1' AND sys.schemas.Name ='dbo'
)

Query2:
SELECT @ColumnNamesWhenNoIdentity = COALESCE(@ColumnNamesWhenNoIdentity + ',', '') + Name +'= SOURCE.'+Name
FROM sys.columns WITH(NOLOCK) WHERE is_identity != 1 AND object_id =
(SELECT sys.objects.object_id FROM sys.objects WITH(NOLOCK)
INNER JOIN sys.schemas WITH(NOLOCK) ON sys.objects.schema_id = sys.schemas.schema_id
WHERE sys.objects.TYPE = 'U' AND sys.objects.Name = 'Testing2' AND sys.schemas.Name ='dbo'
)

View 8 Replies View Related

SQL Server 2014 :: Large Table With Multiple Search Columns

Jun 23, 2015

I've a database with a table that has 16 columns that are searchable. There can be a numerous combination of those columns used for searching...

In this case the best solution is to create an index on each column individually or at least the most used?

View 7 Replies View Related

Can One Text Box's Contents Show Partially In One Font And Partially In Another?

Dec 20, 2007

would I have to use an image (or two textboxes) in RS if my intention is to mix two different fonts at the same time in the same textbox?

View 3 Replies View Related

SQL Server 2014 :: Import Data From Excel To Table - Null Instead Of 0 In Columns

Oct 29, 2013

I have a excel sheet with some data and blank columns. I have a ssis package using to import data from excel to sql table. For blank excel columns it is importing as null instead i want to show them as '0'. If data comes in it should update the data.

View 8 Replies View Related

SQL Server Admin 2014 :: Create Dynamic Columns In Temp Table?

Jun 9, 2014

I want to generate dynamic temp table so, from one strored procedure am getting an some feilds as shown below

CM_id,CM_Name,[Transaction_Month],[Transaction_Year],''[Invoice raised date],''[Payment Received date],''[Payout date],''[Payroll lock date]

for i want to generate table for the above feilds with datatype

View 5 Replies View Related

SQL Server 2014 :: Identify Difference Between Two Table Columns And Output Only The Differences

Sep 4, 2015

IF Object_id('GoldenSecurity') IS NOT NULL DROP TABLE dbo.GoldenSecurity;
IF Object_id('GoldenSecurityRowVersion') IS NOT NULL DROP TABLE dbo.GoldenSecurityRowVersion;

CREATE TABLE dbo.GoldenSecurity (securityID INT, CompanyId INT, Securityname VARCHAR(50), issuedate SMALLDATETIME, currencyID INT)

[Code] ......

View 6 Replies View Related

SQL Server 2014 :: How To Update Values Based On Column Into Multiple Columns In Another Table

Jul 31, 2015

I have a table #vert where I have value column. This data needs to be updated into two channel columns in #hori table based on channel number in #vert table.

CREATE TABLE #Vert (FILTER VARCHAR(3), CHANNEL TINYINT, VALUE TINYINT)
INSERT #Vert Values('ABC', 1, 22),('ABC', 2, 32),('BBC', 1, 12),('BBC', 2, 23),('CAB', 1, 33),('CAB', 2, 44) -- COMBINATION OF FILTER AND CHANNEL IS UNIQUE
CREATE TABLE #Hori (FILTER VARCHAR(3), CHANNEL1 TINYINT, CHANNEL2 TINYINT)
INSERT #Hori Values ('ABC', NULL, NULL),('BBC', NULL, NULL),('CAB', NULL, NULL) -- FILTER IS UNIQUE IN #HORI TABLE

One way to achieve this is to write two update statements. After update, the output you see is my desired output

UPDATE H
SET CHANNEL1= VALUE
FROM #Hori H JOIN #Vert V ON V.FILTER=H.FILTER
WHERE V.CHANNEL=1 -- updates only channel1
UPDATE H
SET CHANNEL2= VALUE
FROM #Hori H JOIN #Vert V ON V.FILTER=H.FILTER
WHERE V.CHANNEL=2 -- updates only channel2
SELECT * FROM #Hori -- this is desired output

my channels number grows in #vert table like 1,2,3,4...and so Channel3, Channel4....so on in #hori table. So I cannot keep writing too many update statements. One other way is to pivot #vert table and do single update into #hori table.

View 5 Replies View Related

Table With Two Columns To Pivot

Aug 20, 2014

I have a table with this data structure (Before section)

I have the query below to generate date as in (After section)

But I would like to have (Desired section) view where start and end tasks are next to each other

Here is the query
-----------------------------

SELECT
ID
,[Final] AS [Final Start]
,[Edit] as [Edit Start]
,[Proof] as [Proof Start]
,[Stage] as [Stage Start]
,[Marketing] as [Marketing Start]

[Code] ....

View 2 Replies View Related

Using Two SUM Columns In A Pivot Table

Feb 6, 2008



I currently have a pivot table that is working great but I need to add to it. The below code is giving me the total ServiceTime per date, which is dynamic. I need to split this service time out depending on the stage of this note.

1) If the note has been signed
2) If the note has been signed and countersigned

SUM(CASE WHEN countersigned_id IS NULL AND signed_id IS NOT NULL THEN ISNULL(d.time_face, 0) + ISNULL(d.time_other, 0) ELSE
0 END) as PendingServiceTime,

SUM(CASE WHEN countersigned_id IS NOT NULL THEN ISNULL(d.time_face, 0) + ISNULL(d.time_other, 0) ELSE
0 END) as ApprovedServiceTime

How do I add this to my pivot table query? I am thinking that I need to have two seperate queries and join them together some how.


SELECT lastname + ', ' + firstname as FullName, [12/3/2007], [12/4/2007], [12/5/2007]
FROM (SELECT p.LastName, p.FirstName, t.ServiceDate,

ISNULL(d.time_face, 0) + ISNULL(d.time_other, 0) AS ServiceTime

FROM dbo.allNotes(8) AS t
LEFT JOIN dbo.note_Collateral_provider AS d ON d.note_Collateral_id = t.ID
LEFT JOIN dbo.Personnel as p ON d.personnel_id = p.ID
LEFT JOIN dbo.Clients as c on t.ClientID = c.ID
LEFT JOIN fPayor(8) fp on fp.noteId = t.id and fp.dbTable = 'collateral'
LEFT JOIN dbo.payor py ON py.ID = substring(fp.fPayorName, 41, 19)
LEFT JOIN dbo.payorinfo pyInfo ON pyInfo.ID = py.payorinfoid
WHERE t.AgencyID = 8 AND t.tableName = 'collateral'
AND t.not_billable_reason_id IS NULL AND VOID_ID IS NULL
AND ((t.signed_id IS NOT NULL AND t.countersigned_id IS NULL) OR (t.countersigned_id IS NOT NULL))
AND t.ServiceDate BETWEEN CONVERT(DATETIME, '12/03/2007') AND CONVERT(DATETIME, '12/05/2007')
) rs Pivot (SUM(rs.ServiceTime) FOR rs.ServiceDate IN ([12/3/2007], [12/4/2007], [12/5/2007]

View 3 Replies View Related

SQL Server 2014 :: Union All Statement Hangs Engine?

Sep 9, 2014

I have TSQL query using UNION ALL. First subset returns 92 rows, second subset return 0 (ZERO) rows. Now if I execute them separately they execute subsecond. If I put UNION ALL between them then it hangs forever exactly on 88s row.

I understand I need to provide execution plans, actual queries etc but before going into all this details, why on earth even in theory UNION ALL with empty result will result in a hang of the first subset?

Based on estimated execution plan 2 subqueries are performed in parallel and then CONCATENTION is being used which shall result with just first subset immediately displayed in result.

Estimated execution plan attached, I can not get actual execution plan because query never completes.

View 9 Replies View Related

Help With Flattening A Table

Jun 2, 2006

Hi All,

I have a database that stores some data from two questionnaires. Table One has been created in a fairly straightforward manner. It has the following fields:

ID, variable1, variable2, variable3, ..., variablen

Table Two is a bit more complicated. I'm guessing whoever created it wanted to do things a bit more "efficiently" and it has the following fields.

ID, variable, response.



If I wanted to, say, extract variables 2, 3 and 5 from Table One I could write:

SELECT ID, variable2, variable3, variable 5 FROM TableOne

I don't know how to do a similar thing with Table 2 using only one statement. ie, I can only think of doing:

SELECT ID, Response FROM Table2 WHERE variable = 2

SELECT ID, Response FROM Table2 WHERE variable = 3

SELECT ID, Response FROM Table2 WHERE variable = 5



This isn't much good to me as I need the data from Table Two in a single table. Once I've done that, I need to join it to Table One. Ordinarily I'd know how to do that, but I don't know how to do given the format of Table Two.

So... I was wondering if anyone could offer advice on how to "flatten" Table Two?



I would like to be able to do something like:

SELECT t1.ID, t1.variable1, t1.variable2, t2.variable6, t2.variable7

FROM TableOne t1 JOIN TableTwo t2 ON t1.ID = t2.ID



Any help would be much appreciated!

View 1 Replies View Related

Help With A Pivot Table (dynamic Columns)

Feb 15, 2008

I have the following Pivot Table:



Code Snippet
Declare @tblEquipment Table
(
numEquipmentID INT,
txtManufacturer nvarchar(30),
txtModel nvarchar(30)
)
Declare @tblEquipmentFields Table
(
numFieldNameID INT,
txtFieldName nvarchar(25)
)
Declare @tblEquipmentDetails Table
(
numEquipmentDetailsID INT,
numEquipmentID INT,
numFieldNameID INT,
txtFieldValue nvarchar(30)
)
Insert INTO @tblEquipment Values(23, 'Dell', 'Optiplex 270')
Insert INTO @tblEquipment Values(26, 'Dell', '1705FP')
Insert INTO @tblEquipment Values(42, 'Dell', 'Optiples 745')
Insert INTO @tblEquipmentFields Values(1, 'Monitor Size')
Insert INTO @tblEquipmentFields Values(2, 'Processor Type')
Insert INTO @tblEquipmentDetails Values(1077, 23, 2, 'P4M')
Insert INTO @tblEquipmentDetails Values(1146, 26, 1, '17')
Insert INTO @tblEquipmentDetails Values(1026, 42, 2, 'P4 Dual Core')
Select numEquipmentID As EquipmentID, [Monitor Size], [Processor Type]
From
(Select a.numEquipmentID, txtManufacturer, txtModel, txtFieldName, txtFieldValue
From @tblEquipment a JOIN
@tblEquipmentDetails b ON
a.numEquipmentID = b.numEquipmentID
JOIN @tblEquipmentFields c ON
b.numFieldNameID = c.numFieldNameID
) As SourceTable
Pivot
(
Max(txtFieldValue)
For txtFieldName IN ([Monitor Size], [Processor Type])
) As PivotTable






What I'm wondering is if it's possible to have the columns change dynamically. For example:

If lets say I only want the record with numEquipmentID of 23 to show I only want its corresponding information to show

EquipmentID ProcessorType
23 P4M

Now lets say that I want to bring back an additional record, like 23 and 26 I would like the columns to change to the following

EquipmentID ProcessorType Monitor Size
23 P4M NULL
26 NULL 17

So in essence a column will be added based on the equipmentID. Thanks in advanced.

View 4 Replies View Related

SQL Server 2014 :: Pivots Date Ranges With Union Data?

Aug 21, 2015

What I am trying to accomplish is to make a few extra columns with specified date ranges.

I have FY14 date range in the parameters at the top .. I would like to add a FY 15 column so the year would move up by 1.and also I need to add 2 more columns Prior year current month and This year, current month.

<code>
DECLARE @Fy14_start datetime
DECLARE @Fy14_end datetime
SET @Fy14_start = '2013-07-01'
SET @Fy14_end = '2014-06-30'
SELECT x.ACCOUNT_NAME, X.STATUS_CODE, COUNT(X.PATIENT_CODE) AS FY14

[code]....

View 2 Replies View Related

PIVOT Multiple Columns In Test Table

Apr 24, 2014

-- Here's a test table where I'm trying to workout how to Pivot more than one column.

-- Drop the Temp Table
IF (SELECT Object_id('tempdb..#Test_Pivot_Example')) <> 0
BEGIN
DROP TABLE #Test_Pivot_Example
END

[Code] ....

Once I have worked this out then I need to dynamic populate the IN ([1] etc with the val;ue sin field [SIZE])

but one step at a time trying to workout pivot on more than one column.

View 4 Replies View Related

SQL Server 2014 :: How To Get A Pivot Statement Without Aggregate

Jun 11, 2014

I want to pivot a table something like this .I pivoted it successfully but the results are not correct.

Here is the example :

install-Name Fiscal year Question Answer
Washington 2010 what is the reason for install? tttttt ggg yttt o
washington 2010 reason id 12345
washington 2010 install start date 10/10/2010
washington 2010 install end date 10/12/2010
washington 2010 install status successfull

[code]....

I want the above data to get pivoted like this

Install-name | Fiscal year |what is the reason for install? | reason id | install start date | install end date |
install status |Do you feel the install is incomplete? | Is the expiration of 90 days exceeded? |

washington | 2010 | tttttt ggg yttt o | 12345 | 10/10/2010 | 10/12/2010 |
successful | | |
washington | 2010 | trtbnbthwgt hrgthjrt | - | 12/3/2010 | 12/8/2010 |
| | |
washington | 2011 | sbjeh dhebwdh dbjw | 345 | 10/10/2010 | 10/12/2010 |
successful | No | Yes ,but b b b b |

View 2 Replies View Related

Reporting Services :: Pivot A Table Of Data With Multiple Columns?

Nov 20, 2015

Running SQL Server 2005, trying to develop an SSRS report to basically pivot a table of data with multiple columns.

Here's the basic source table:

Day    Cases   Referrals    Vends
1         291          0             0
2         293          1             0
3         293          1             1

And I want to display it as:

Day             1       2       3
Cases         291   293   293
Referrals       0        1      1
Vends           0        0      1

I thought I could use a matrix for this but I can't seem to get it worked out. Is this even possible?

The Day number is meant to represent the day of the month and the user would input a start and ending date parameter.

View 8 Replies View Related

SQL Server 2014 :: Get SUM Of The Sum Of Three Columns And All Three Columns Have Nulls?

Jul 13, 2015

Basically I need to get the SUM of the sum of three columns and all three columns have nulls. To make it more complicated, the result set must return the top 20 in order desc as well.

I keep facing different issues whether I try and use Coalesce, IsNull, Sum, count, anything. My query never returns anything but 0 or NULL regardless of if I am trying to build a CTE or just use a query.

So I'm using Col A to get the TOP 20 in order (which is fine) but also trying to add together the sums of Col A + Col B + Col C for each of the twenty rows...

View 2 Replies View Related

SQL Server 2014 :: Generate Column Numbers Using Pivot Command

Jul 1, 2014

I have the following SQL which i want to convert to a stored procedure having dynamic SQL to generate column numbers (1 to 52) for Sale_Week. Also, I want to call this stored procedure from Excel using VBA, passing 2 parameters to stored procedure in SQL Server
e.g,

DECLARE @KPI nvarchar(MAX) = 'Sales Value with Innovation' DECLARE @Country nvarchar(MAX) = 'UK'

I want to grab the resultant pivoted table back into excel. how to do it?

USE [Database_ABC]
GO

DECLARE @KPI nvarchar(MAX) = 'Sales Value with Innovation'
DECLARE @Country nvarchar(MAX) = 'UK'

SELECT [sCHAR],[sCOUNTRY],[Category],[Manufacturer],[Brand],[Description],[1],[2],
[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],

[Code] ....

View 9 Replies View Related

SQL Server 2014 :: How To Pivot Output Data Into Multiple Rows

Nov 4, 2015

Is it possible to pivot the output data into multiple rows?

I wanted one row for deleted data and another row for Inserted data, I was looking at UNION ALL and CROSS APPLY but to no avail.

View 9 Replies View Related

How To Validate Flat File Data Against Not Null Columns Of A Table?

Nov 27, 2007

Hi all,

I am facing a problem on validating the data from a flat file while inserting the data into the destination table of sql server 2005 database. In my package, i have to validate the input data whether the values are coming as null or not, before inserting into the destination database. The flat file may not contain data for all NOT NULL columns. I have to find out that row(s) and reject the record. If the rows are coming as Null for the Not Null columns, the OLEDB Destination throws OLEDB exception for the constraint.

To resolve this, i have an script component in data flow, to check whether the input data is coming as null. I have added the output column of boolean type to the script component, it will be assigned to TRUE when there is null for the Not Null column in the script.

And in the follwing conditional split, i am checking the flag for TRUE to reject the record.

Is there any other way to handle this validation?

Regards
Madhav

View 6 Replies View Related

SQL Server 2014 :: Pivot Data Results - Concatenate All Products Of Client Together

Mar 26, 2015

I am trying to pivot my data results so that instead of showing multiple rows for each product a client has it will show one line for each client and concatenate all of their products together.

For example, if I run the following query:

SELECT
[Year],
[Client_Name],
[Product_Name]
FROM My.dbo.Table
GROUP BY
[Year],
[Client_Name],
[Product_Name]

I get the following result set:

YearClient_NameProduct_Name
2014BobHosting
2014BobDevelopment
2014BobTesting
2014FredDevelopment
2014FredConsulting
2014MaxConsulting
2015BobHosting
2015BobTesting

What I want to get back as the result set is the following:

YearClient NameProduct-List
2014BobHosting-Development-Testing
2014FredDevelopment-Consulting
2014MaxConsulting
2015BobHosting-Testing

So, I would only get one record back for each Client for each Year with a list of all of their products concatenated together.

View 2 Replies View Related

SQL Server 2014 :: Change Daily Info To Weekly Periods In Pivot Report

Sep 25, 2015

I create a report base on categories and sales of goods. Now I have Daily Info about all Products.

But I Need to present this report base on weekly periods. in pivotal format.

I family with pivotal format but change between daily report and weekly report is ambiguous for me.

View 3 Replies View Related

SQL 2012 :: Partially Contained Database And Temp Table Creation

Nov 20, 2014

As far as I know temp tables/objects will be created inside the default filegroup of the partially contained database and not in tempdb. Is it possible to either define a set of files dedicated to temp objects or define a second partially contained database dedicated to temp objects like tempdb?

View 0 Replies View Related

Power Pivot :: Auto Refresh Excel Table (Not Pivot Table) Using Data Source

Jul 8, 2015

Is it possible to generate automatic refresh of excel 2013 table which displays some table of a power pivot model on file open?? I dont want to use pivottable (which supports this ...)

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved