SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Split row into columns


Split row into columns

Author
Message
DBayliss
DBayliss
SSC Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
Points: 38 Visits: 157
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
Attachments
example.xlsx (21 views, 10.00 KB)
Eirikur Eiriksson
Eirikur Eiriksson
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15260 Visits: 18607
This is not a dynamic solution, the number of columns are set in the code.
Cool

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
DBayliss
DBayliss
SSC Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
Points: 38 Visits: 157
Thank you very much for your help.
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26275 Visits: 17553
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 Modens 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)
rhythmk
rhythmk
SSC Eights!
SSC Eights! (916 reputation)SSC Eights! (916 reputation)SSC Eights! (916 reputation)SSC Eights! (916 reputation)SSC Eights! (916 reputation)SSC Eights! (916 reputation)SSC Eights! (916 reputation)SSC Eights! (916 reputation)

Group: General Forum Members
Points: 916 Visits: 1078
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/
:-)
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26275 Visits: 17553
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? :-P

_______________________________________________________________

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 Modens 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)
Eirikur Eiriksson
Eirikur Eiriksson
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15260 Visits: 18607
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? :-P


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.
Cool
Here are some numbers with POC (in order of appearance:-P )
(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.

Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26275 Visits: 17553
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 Modens 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)
Eirikur Eiriksson
Eirikur Eiriksson
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15260 Visits: 18607
Few more numbers (500K rows), queries in the same order as before.
Take note of the high CPU cost of the LEAD function!
Cool

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.

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search