May 12, 2014 at 2:23 am
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
May 12, 2014 at 3:59 am
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[/url] by Aaron Bertrand
May 12, 2014 at 4:23 am
Thank you very much for your help.
May 12, 2014 at 7:41 am
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 13, 2014 at 1:23 am
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
https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂
May 13, 2014 at 7:19 am
rhythmk (5/13/2014)
With the help of Eirikur Eiriksson's code.You acn try this.
;WITH testAS
(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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 13, 2014 at 7:35 am
Sean Lange (5/13/2014)
rhythmk (5/13/2014)
With the help of Eirikur Eiriksson's code.You acn try this.
;WITH testAS
(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:-P )
[font="Courier New"](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.[/font]
May 13, 2014 at 7:49 am
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 13, 2014 at 8:05 am
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,
[font="Courier New"]
(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.[/font]
Clustered index, no POC
[font="Courier New"](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.[/font]
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply