June 19, 2013 at 2:07 pm
Hi am trying to change row values to column based on ControlNO & Seq column. (Please see @currenttable)
InspInterval --> int0,int1,int2,int3
ChkProcedureKey --> p_chkproc0,p_chkproc1,p_chkproc2,p_chkproc3
IntUnit --> intunit0,intunit1,intunit2,intunit3
I did try to do pivot but was unsuccessful 🙁
Included is the sql of what the table looks like currently and how I has to be converted.. FYI, they are 208503 records in the table if that matters on the method we look into.
Declare @CurrentTable Table([ControlNo] [nvarchar](15),[MODELKEY] [int],[SEQ] [bigint],[InspInterval] [int],[ChkProcedureKey] [int],[IntUnit] [nvarchar](1))
INSERT INTO @CurrentTable
select '020468','7996','1','2','99','Y' UNION
select '020468','7996','2','6','26','M' UNION
select '020468','7996','3','6','27','M' UNION
select '020468','7996','4','12','28','M'UNION
select '03020/51001048','12307','1','0','99','M' UNION
select '03020/51001048','12307','2','0','363','M'UNION
select '03020/51001048','12307','3','0','364','M'
SELECT * FROM @CurrentTable
Declare @RequiredTable Table ([ControlNo] [nvarchar](15),[MODELKEY] [int],[int0] [int],[int1] [int],[int2] [int],[int3] [int]
,[p_chkproc0] [int],[p_chkproc1] [int],[p_chkproc2] [int],[p_chkproc3] [int],[intunit0] [nvarchar](1),[intunit1] [nvarchar](1),[intunit2] [nvarchar](1),[intunit3] [nvarchar](1)
)
INSERT INTO @RequiredTable
select '020468','7996','2','6','6','12','99','26','27','28','Y','M','M','M' UNION
select '03020/51001048','12307','0','0','0','','99','363','364','','M','M','M',''
SELECT * FROM @RequiredTable
June 20, 2013 at 1:23 am
A CROSS TAB will give you the desired results
SELECTControlNo, MODELKEY,
SUM( CASE WHEN SEQ = 1 THEN InspInterval ELSE 0 END ) AS int0,
SUM( CASE WHEN SEQ = 2 THEN InspInterval ELSE 0 END ) AS int1,
SUM( CASE WHEN SEQ = 3 THEN InspInterval ELSE 0 END ) AS int2,
SUM( CASE WHEN SEQ = 4 THEN InspInterval ELSE 0 END ) AS int3,
SUM( CASE WHEN SEQ = 1 THEN ChkProcedureKey ELSE 0 END ) AS p_chkproc0,
SUM( CASE WHEN SEQ = 2 THEN ChkProcedureKey ELSE 0 END ) AS p_chkproc1,
SUM( CASE WHEN SEQ = 3 THEN ChkProcedureKey ELSE 0 END ) AS p_chkproc2,
SUM( CASE WHEN SEQ = 4 THEN ChkProcedureKey ELSE 0 END ) AS p_chkproc3,
MAX( CASE WHEN SEQ = 1 THEN IntUnit ELSE '' END ) AS intunit0,
MAX( CASE WHEN SEQ = 2 THEN IntUnit ELSE '' END ) AS intunit1,
MAX( CASE WHEN SEQ = 3 THEN IntUnit ELSE '' END ) AS intunit2,
MAX( CASE WHEN SEQ = 4 THEN IntUnit ELSE '' END ) AS intunit3
FROM@CurrentTable
GROUP BY ControlNo, MODELKEY
Please check the below mentioned links for more details on CROSS TABS
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns[/url]
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs[/url]
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
June 20, 2013 at 6:37 am
Thank you Dhasian you have showed me a new approach to fix this issue (I thought that Pivot is the only way)
I appreciate your help 🙂
Also I have an additional question there is a datetime field and varchar
(CycleDate --> 2006-06-01 00:00:00.000)
I used "MIN"
MIN( CASE WHEN SEQ = 1 THEN CycleDate ELSE '2040-01-01 00:00:00.000' END ) AS CycleDate0
Is this correct or is there an differ approach for datetime and Varchar field.
June 20, 2013 at 6:46 am
knakka99 (6/20/2013)
Thank you Dhasian you have showed me a new approach to fix this issue (I thought that Pivot is the only way)I appreciate your help 🙂
Also I have an additional question there is a datetime field and varchar
(CycleDate --> 2006-06-01 00:00:00.000)
I used "MIN"
MIN( CASE WHEN SEQ = 1 THEN CycleDate ELSE '2040-01-01 00:00:00.000' END ) AS CycleDate0
Is this correct or is there an differ approach for datetime and Varchar field.
Your approach should work unless you have a CycleDate greater than '2040-01-01 00:00:00.000' in your table. So, it is not in-correct as such.
But a better way would be to use the same approach we had used for VARCHAR, i.e;
MAX( CASE WHEN SEQ = 1 THEN CycleDate ELSE NULL END ) AS CycleDate0
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
June 20, 2013 at 8:13 am
Thank you once again on the feedback.
🙂
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply