Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Split row into columns Expand / Collapse
Author
Message
Posted Monday, May 12, 2014 2:23 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, May 12, 2014 4:22 AM
Points: 13, Visits: 103
Hi All,

I've enclosed an excel spreadsheet to highlight the issue I'm currently encountering.

I'm trying to dynamically convert a row into columns, but I'm not sure the best way to go about this.

Any tips/advice would be greatly appreciated.

Thanks


  Post Attachments 
example.xlsx (17 views, 10.04 KB)
Post #1569711
Posted Monday, May 12, 2014 3:59 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 1:17 AM
Points: 1,294, Visits: 3,722
This is not a dynamic solution, the number of columns are set in the code.


USE tempdb;
GO

DECLARE @POLHIST TABLE
(
POLICY_DETAILS_ID VARBINARY(16) NOT NULL
,HISTORY_ID INT NOT NULL
,CODE_ID VARCHAR(12) NOT NULL
);
INSERT INTO @POLHIST (POLICY_DETAILS_ID,HISTORY_ID,CODE_ID)
VALUES
(0x0000EE3FC15640D791EA76DD43138797,1,'MMALIA01')
,(0x0000EE3FC15640D791EA76DD43138797,1,'MMALIA02')
,(0x0000EE3FC15640D791EA76DD43138797,1,'MMALIA06')
,(0x0000EE3FC15640D791EA76DD43138797,1,'MMALIA14')
,(0x0000EE3FC15640D791EA76DD43138797,2,'MMALIA02')
,(0x0000EE3FC15640D791EA76DD43138797,2,'MMALIA06')
,(0x0000EE3FC15640D791EA76DD43138797,2,'MMALIA14');

;WITH CODE_COLS AS
(
SELECT
ROW_NUMBER() OVER (PARTITION BY PH.HISTORY_ID ORDER BY (SELECT NULL)) AS PH_RID
,PH.HISTORY_ID
,PH.CODE_ID
FROM @POLHIST PH
)
SELECT
PH.POLICY_DETAILS_ID
,PH.HISTORY_ID
,C1.CODE_ID AS CODE1
,C2.CODE_ID AS CODE2
,C3.CODE_ID AS CODE3
,C4.CODE_ID AS CODE4
,C5.CODE_ID AS CODE5
FROM
(
SELECT
ROW_NUMBER() OVER (PARTITION BY PH.HISTORY_ID ORDER BY (SELECT NULL)) AS PH_RID
,PH.POLICY_DETAILS_ID
,PH.HISTORY_ID
FROM @POLHIST PH
) AS PH
LEFT OUTER JOIN CODE_COLS C1 ON PH.HISTORY_ID = C1.HISTORY_ID AND PH.PH_RID = C1.PH_RID - 0
LEFT OUTER JOIN CODE_COLS C2 ON PH.HISTORY_ID = C2.HISTORY_ID AND PH.PH_RID = C2.PH_RID - 1
LEFT OUTER JOIN CODE_COLS C3 ON PH.HISTORY_ID = C3.HISTORY_ID AND PH.PH_RID = C3.PH_RID - 2
LEFT OUTER JOIN CODE_COLS C4 ON PH.HISTORY_ID = C4.HISTORY_ID AND PH.PH_RID = C4.PH_RID - 3
LEFT OUTER JOIN CODE_COLS C5 ON PH.HISTORY_ID = C5.HISTORY_ID AND PH.PH_RID = C5.PH_RID - 4
WHERE PH.PH_RID = 1;

Results
POLICY_DETAILS_ID                  HISTORY_ID  CODE1        CODE2        CODE3        CODE4        CODE5
---------------------------------- ----------- ------------ ------------ ------------ ------------ ------------
0x0000EE3FC15640D791EA76DD43138797 1 MMALIA01 MMALIA02 MMALIA06 MMALIA14 NULL
0x0000EE3FC15640D791EA76DD43138797 2 MMALIA02 MMALIA06 MMALIA14 NULL NULL


And just for fun, a window function version of the same, works on 2012/2014
USE tempdb;
GO

DECLARE @POLHIST TABLE
(
POLICY_DETAILS_ID VARBINARY(16) NOT NULL
,HISTORY_ID INT NOT NULL
,CODE_ID VARCHAR(12) NOT NULL
);
INSERT INTO @POLHIST (POLICY_DETAILS_ID,HISTORY_ID,CODE_ID)
VALUES
(0x0000EE3FC15640D791EA76DD43138797,1,'MMALIA01')
,(0x0000EE3FC15640D791EA76DD43138797,1,'MMALIA02')
,(0x0000EE3FC15640D791EA76DD43138797,1,'MMALIA06')
,(0x0000EE3FC15640D791EA76DD43138797,1,'MMALIA14')
,(0x0000EE3FC15640D791EA76DD43138797,2,'MMALIA02')
,(0x0000EE3FC15640D791EA76DD43138797,2,'MMALIA06')
,(0x0000EE3FC15640D791EA76DD43138797,2,'MMALIA14');

SELECT * FROM (
SELECT
ROW_NUMBER() OVER (PARTITION BY PH.HISTORY_ID ORDER BY (SELECT NULL)) AS PH_RID
,PH.POLICY_DETAILS_ID
,PH.HISTORY_ID
,PH.CODE_ID AS PH_CODE1
,LEAD(PH.CODE_ID,1,NULL) OVER (PARTITION BY PH.HISTORY_ID ORDER BY (SELECT NULL)) AS PH_CODE2
,LEAD(PH.CODE_ID,2,NULL) OVER (PARTITION BY PH.HISTORY_ID ORDER BY (SELECT NULL)) AS PH_CODE3
,LEAD(PH.CODE_ID,3,NULL) OVER (PARTITION BY PH.HISTORY_ID ORDER BY (SELECT NULL)) AS PH_CODE4
,LEAD(PH.CODE_ID,4,NULL) OVER (PARTITION BY PH.HISTORY_ID ORDER BY (SELECT NULL)) AS PH_CODE5
FROM @POLHIST PH
) AS X WHERE X.PH_RID = 1

If you need to dynamically add columns, then take a look at Script to create dynamic PIVOT queries in SQL Server by Aaron Bertrand
Post #1569741
Posted Monday, May 12, 2014 4:23 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, May 12, 2014 4:22 AM
Points: 13, Visits: 103
Thank you very much for your help.
Post #1569748
Posted Monday, May 12, 2014 7:41 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:28 PM
Points: 13,103, Visits: 11,933
Another way to do this is by using a cross tab. I used Erikur's data as a starting point. This will be better performance wise because it doesn't have to hit the table more than once.

;WITH CODE_COLS AS
(
SELECT
ROW_NUMBER() OVER (PARTITION BY PH.HISTORY_ID ORDER BY (SELECT NULL)) AS PH_RID
,PH.HISTORY_ID
,PH.CODE_ID
, POLICY_DETAILS_ID
FROM @POLHIST PH
)

select ph.POLICY_DETAILS_ID,
ph.HISTORY_ID,
MAX(case when ph.PH_RID = 1 then CODE_ID end) as CODE1,
MAX(case when ph.PH_RID = 2 then CODE_ID end) as CODE2,
MAX(case when ph.PH_RID = 3 then CODE_ID end) as CODE3,
MAX(case when ph.PH_RID = 4 then CODE_ID end) as CODE4,
MAX(case when ph.PH_RID = 5 then CODE_ID end) as CODE5
from CODE_COLS ph
group by ph.POLICY_DETAILS_ID, ph.HISTORY_ID

You can read more about the concept by reading the articles in my signature about cross tabs.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1569848
Posted Tuesday, May 13, 2014 1:23 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 5:04 AM
Points: 558, Visits: 881
With the help of Eirikur Eiriksson's code.You acn try this.
;WITH test
AS
(SELECT POLICY_DETAILS_ID,HISTORY_ID,CODE_ID,
ROW_NUMBER() OVER(PARTITION BY POLICY_DETAILS_ID,HISTORY_ID ORDER BY code_id) AS rn
FROM @POLHIST)
SELECT POLICY_DETAILS_ID,HISTORY_ID,
MAX(CASE WHEN rn = 1 THEN code_id END) AS Code1,
MAX(CASE WHEN rn = 2 THEN code_id END) as Code2,
MAX(CASE WHEN rn = 3 THEN code_id END) as Code3,
MAX(CASE WHEN rn = 4 THEN code_id END ) as Code4
FROM test
GROUP BY POLICY_DETAILS_ID,HISTORY_ID



--rhythmk
------------------------------------------------------------------
To post your question use below link

http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1570162
Posted Tuesday, May 13, 2014 7:19 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:28 PM
Points: 13,103, Visits: 11,933
rhythmk (5/13/2014)
With the help of Eirikur Eiriksson's code.You acn try this.
;WITH test
AS
(SELECT POLICY_DETAILS_ID,HISTORY_ID,CODE_ID,
ROW_NUMBER() OVER(PARTITION BY POLICY_DETAILS_ID,HISTORY_ID ORDER BY code_id) AS rn
FROM @POLHIST)
SELECT POLICY_DETAILS_ID,HISTORY_ID,
MAX(CASE WHEN rn = 1 THEN code_id END) AS Code1,
MAX(CASE WHEN rn = 2 THEN code_id END) as Code2,
MAX(CASE WHEN rn = 3 THEN code_id END) as Code3,
MAX(CASE WHEN rn = 4 THEN code_id END ) as Code4
FROM test
GROUP BY POLICY_DETAILS_ID,HISTORY_ID



Is there an echo in here?


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1570285
Posted Tuesday, May 13, 2014 7:35 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 1:17 AM
Points: 1,294, Visits: 3,722
Sean Lange (5/13/2014)
rhythmk (5/13/2014)
With the help of Eirikur Eiriksson's code.You acn try this.
;WITH test
AS
(SELECT POLICY_DETAILS_ID,HISTORY_ID,CODE_ID,
ROW_NUMBER() OVER(PARTITION BY POLICY_DETAILS_ID,HISTORY_ID ORDER BY code_id) AS rn
FROM @POLHIST)
SELECT POLICY_DETAILS_ID,HISTORY_ID,
MAX(CASE WHEN rn = 1 THEN code_id END) AS Code1,
MAX(CASE WHEN rn = 2 THEN code_id END) as Code2,
MAX(CASE WHEN rn = 3 THEN code_id END) as Code3,
MAX(CASE WHEN rn = 4 THEN code_id END ) as Code4
FROM test
GROUP BY POLICY_DETAILS_ID,HISTORY_ID



Is there an echo in here?


It is not an echo and interestingly enough, performs quite well, on 500k test without an POC index, it came out first.
I added an POC index which removed the sort operator from the first two queries but not this one. The presence of the index also removes the parallelism from the first two.

Here are some numbers with POC (in order of appearance )
(100000 row(s) affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TBL_POLICYHISTORY'. Scan count 1, logical reads 2730, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

SQL Server Execution Times:
CPU time = 5647 ms, elapsed time = 5848 ms.
Warning: Null value is eliminated by an aggregate or other SET operation.

(100000 row(s) affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TBL_POLICYHISTORY'. Scan count 1, logical reads 2730, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

SQL Server Execution Times:
CPU time = 733 ms, elapsed time = 1600 ms.

(100000 row(s) affected)
Table 'TBL_POLICYHISTORY'. Scan count 5, logical reads 2994, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Warning: Null value is eliminated by an aggregate or other SET operation.

(1 row(s) affected)

SQL Server Execution Times:
CPU time = 1545 ms, elapsed time = 1967 ms.
Post #1570302
Posted Tuesday, May 13, 2014 7:49 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:28 PM
Points: 13,103, Visits: 11,933
Interesting. The only actual difference was in the partition predicate of the ROW_NUMBER function. Without having POLICY_DETAILS_ID it adds a very expensive sort to the execution plan. Not sure I understand why that is.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1570319
Posted Tuesday, May 13, 2014 8:05 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 1:17 AM
Points: 1,294, Visits: 3,722
Few more numbers (500K rows), queries in the same order as before.
Take note of the high CPU cost of the LEAD function!


Heap, no index,


(100000 row(s) affected)
Table 'TBL_POLICYHISTORY'. Scan count 5, logical reads 2907, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 9611 ms, elapsed time = 3544 ms.

(100000 row(s) affected)
Table 'TBL_POLICYHISTORY'. Scan count 5, logical reads 2907, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 1340 ms, elapsed time = 1526 ms.
Warning: Null value is eliminated by an aggregate or other SET operation.

(100000 row(s) affected)
Table 'TBL_POLICYHISTORY'. Scan count 5, logical reads 2907, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 1483 ms, elapsed time = 1528 ms.
Warning: Null value is eliminated by an aggregate or other SET operation.


Clustered index, no POC

(100000 row(s) affected)
Table 'TBL_POLICYHISTORY'. Scan count 5, logical reads 3203, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 10202 ms, elapsed time = 3662 ms.

(100000 row(s) affected)
Table 'TBL_POLICYHISTORY'. Scan count 5, logical reads 3203, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 1248 ms, elapsed time = 1367 ms.
Warning: Null value is eliminated by an aggregate or other SET operation.

(100000 row(s) affected)
Table 'TBL_POLICYHISTORY'. Scan count 5, logical reads 3203, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 1529 ms, elapsed time = 1533 ms.
Warning: Null value is eliminated by an aggregate or other SET operation.
Post #1570338
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse