July 7, 2010 at 8:15 am
Hi all,
I am using SSIS to do Pivot but some rows are going to error, I cannot findout why, as all the data looks good to me...
Can anyone tell me the code to do the pivoting using SQL statement:
here is my sample data...
CREATE TABLE [dbo].[t6](
[sno] [nvarchar](14) NULL,
[DATE] [varchar](12) NULL,
[setno] [nvarchar](5) NULL,
[type] [nvarchar](3) NULL,
[code_des] [nvarchar](4) NULL,
[code_no] [int] NULL,
[mygroup] [int] NULL
)
INSERT INTO t6 ( sno,DATE,setno,type,code_des,code_no,mygroup) VALUES ( '888888', 'Jun 29 2010 ', '999', 'KK', 'K000', 1, 33)
INSERT INTO t6 ( sno,DATE,setno,type,code_des,code_no,mygroup) VALUES ( '32000004', 'Jun 29 2010 ', '888', 'XX', 'X123', 1, 811)
INSERT INTO t6 ( sno,DATE,setno,type,code_des,code_no,mygroup) VALUES ( '32000004', 'Jun 29 2010 ', '888', 'XX', 'D123', 2, 811)
INSERT INTO t6 ( sno,DATE,setno,type,code_des,code_no,mygroup) VALUES ( '32000004', 'Jun 29 2010 ', '888', 'GG', 'I123', 3, 811)
INSERT INTO t6 ( sno,DATE,setno,type,code_des,code_no,mygroup) VALUES ( '46600001', 'Jun 29 2010 ', '879', 'ZZ', 'H123', 1, 1419)
INSERT INTO t6 ( sno,DATE,setno,type,code_des,code_no,mygroup) VALUES ( '46600001', 'Jun 29 2010 ', '879', 'ZZ', 'K123', 2, 1419)
INSERT INTO t6 ( sno,DATE,setno,type,code_des,code_no,mygroup) VALUES ( '46600001', 'Jun 29 2010 ', '879', 'JK', 'J123', 3, 1419)
INSERT INTO t6 ( sno,DATE,setno,type,code_des,code_no,mygroup) VALUES ( '57040013', 'Jun 29 2010 ', '567', 'PP', 'H123', 1, 1686)
INSERT INTO t6 ( sno,DATE,setno,type,code_des,code_no,mygroup) VALUES ( '57040013', 'Jun 29 2010 ', '567', 'PP', 'D123', 2, 1686)
INSERT INTO t6 ( sno,DATE,setno,type,code_des,code_no,mygroup) VALUES ( '57040013', 'Jun 29 2010 ', '567', 'PP', 'I123', 3, 1686)
INSERT INTO t6 ( sno,DATE,setno,type,code_des,code_no,mygroup) VALUES ( '77777', 'Jun 29 2010 ', '755', 'ZZ', 'K123', 2, 2521)
INSERT INTO t6 ( sno,DATE,setno,type,code_des,code_no,mygroup) VALUES ( '888888', 'Jun 29 2010 ', '999', 'ZZ', 'J123', 3, 2521)
MYgroup Sno SETNO TYPE Codeno 1 Codeno 2 Codeno 3
811 32000004 888 XX X123 D123
this SSIS pivot is way too complicated...
Any help on this
Thanks
Thanks [/font]
July 7, 2010 at 10:19 am
got it...in TSQL but not in SSIS
SELECT [sno]
,[DATE]
,[setno]
,[type]
,[mygroup]
,max([CODE1]) [CODE1],max([CODE2]) [CODE2],max([CODE3]) [CODE3]
,max([CODE4]) [CODE4],max([CODE5]) [CODE5]
from
(
SELECT [sno] ,[DATE] ,[setno] ,[type] ,[mygroup]
,CASE WHEN code_no = 1 THEN code_des ELSE null END [CODE1]
,CASE WHEN code_no = 2 THEN code_des ELSE null END [CODE2]
,CASE WHEN code_no = 3 THEN code_des ELSE null END [CODE3]
,CASE WHEN code_no = 4 THEN code_des ELSE null END [CODE4]
,CASE WHEN code_no = 5 THEN code_des ELSE null END [CODE5]
FROM [CallCenterDM_Stage].[dbo].[t6]
group by [sno]
,[DATE]
,[setno]
,[type]
,[mygroup]
,CASE WHEN code_no = 1 THEN code_des ELSE null END
,CASE WHEN code_no = 2 THEN code_des ELSE null END
,CASE WHEN code_no = 3 THEN code_des ELSE null END
,CASE WHEN code_no = 4 THEN code_des ELSE null END
,CASE WHEN code_no = 5 THEN code_des ELSE null END
) a
group by [sno]
,[DATE]
,[setno]
,[type]
,[mygroup]
Thanks [/font]
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply