Data arrangement - columns to rows in a specific way
-
-
Just to let you know that the image is not showing here on the thread.
-
CodeIDTypeIDNameOrderGroupId
9776210000061111DTaP/IPV/Hib11
9776310000061141DTaP/IPV/Hib21
9776410000061161DTaP/IPV/Hib31
1540611000006110 6DTaP/IPV pre-school booster41
9776610000061178dTaP/IPV 51
4052810000001182PCV 12
4053010000001172PCV 22
4053210000001142PCV 32
458773016 4MenC 13
4077210000001185Hib/Men C booster43
7142110000061143MMR 14
264268018 3MMR 24
2244981000000119 7Rotavirus 15
2245021000000115 7Rotavirus 25
Output must be:
Name CodeId CodeId CodeId
DTaP/IPV/Hib 977621000006111977631000006114977641000006116
PCV 405281000000118405301000000117405321000000114
MenC 458773016 407721000000118null
MMR 714211000006114264268018 null
Rotavirus 22449810000001192245021000000115null
DTaP/IPV pre-school booster1540611000006110null null
Hib/Men C booster407721000000118null null
-
I want the output in such a way that
if the name has codeid for order - 1,2 and 3, i need the codeid for order-1 in the first column, the codeid for order-2 in the 2nd column and the codeid for order-3 in the 3rd column in a single row in the output
if the name has codeid for order - 1,2, then i need the codeid for order-1 in the first column, the codeid for order-2 in the 2nd column and null in the 3rd column in a single row in the output
if the name has order -4 then i need it in a separate row with codeid for order-4 in the first column, null in the 2nd column and null in the 3rd column in a single row in the output
if the name has order -5 then i need it in a separate row with codeid for order-5 in the first column, null in the 2nd column and null in the 3rd column in a single row in the output
Please help
-
Anju Renjith (3/23/2015)
CodeIDTypeIDNameOrderGroupId9776210000061111DTaP/IPV/Hib11
9776310000061141DTaP/IPV/Hib21
9776410000061161DTaP/IPV/Hib31
1540611000006110 6DTaP/IPV pre-school booster41
9776610000061178dTaP/IPV 51
4052810000001182PCV 12
4053010000001172PCV 22
4053210000001142PCV 32
458773016 4MenC 13
4077210000001185Hib/Men C booster43
7142110000061143MMR 14
264268018 3MMR 24
2244981000000119 7Rotavirus 15
2245021000000115 7Rotavirus 25
Output must be:
Name CodeId CodeId CodeId
DTaP/IPV/Hib 977621000006111977631000006114977641000006116
PCV 405281000000118405301000000117405321000000114
MenC 458773016 407721000000118null
MMR 714211000006114264268018 null
Rotavirus 22449810000001192245021000000115null
DTaP/IPV pre-school booster1540611000006110null null
Hib/Men C booster407721000000118null null
If you can, set this up as ddl (CREATE TABLE) and dml (INSERT...). Folks will be able to run queries against it straight away.
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden -
As Chris in the above post suggested that you will get much quicker response if you supply with create table code too.
Here is the code, which I have done, as this is my first ever response here on this forum:
Table create code:
CREATE TABLE dbo.tblTest1
(
[CodeID] BIGINT NULL,
[TypeID] INT NULL,
[Name] [VARCHAR] (30) NULL,
[Order] INT NULL,
[GroupId] INT NULL
)
INSERT INTO [dbo].[tblTest1]
([CodeID],[TypeID],[Name],[Order],[GroupId])
VALUES(977621000006111,1,'DTaP/IPV/Hib',1,1)
INSERT INTO [dbo].[tblTest1]
([CodeID],[TypeID],[Name],[Order],[GroupId])
VALUES(977631000006114,1,'DTaP/IPV/Hib',2,1)
INSERT INTO [dbo].[tblTest1]
([CodeID],[TypeID],[Name],[Order],[GroupId])
VALUES(977641000006116,1,'DTaP/IPV/Hib',3,1)
INSERT INTO [dbo].[tblTest1]
([CodeID],[TypeID],[Name],[Order],[GroupId])
VALUES(1540611000006110,6,'DTaP/IPV pre-school booster',4,1)
INSERT INTO [dbo].[tblTest1]
([CodeID],[TypeID],[Name],[Order],[GroupId])
VALUES(977661000006117,8,'dTaP/IPV',5,1)
INSERT INTO [dbo].[tblTest1]
([CodeID],[TypeID],[Name],[Order],[GroupId])
VALUES(405281000000118,2,'PCV',1,2)
INSERT INTO [dbo].[tblTest1]
([CodeID],[TypeID],[Name],[Order],[GroupId])
VALUES(405301000000117,2,'PCV',2,2)
INSERT INTO [dbo].[tblTest1]
([CodeID],[TypeID],[Name],[Order],[GroupId])
VALUES(405321000000114,2,'PCV',3,2)
INSERT INTO [dbo].[tblTest1]
([CodeID],[TypeID],[Name],[Order],[GroupId])
VALUES(458773016,4,'MenC',1,3)
INSERT INTO [dbo].[tblTest1]
([CodeID],[TypeID],[Name],[Order],[GroupId])
VALUES(407721000000118,5,'Hib/Men C booster',4,3)
INSERT INTO [dbo].[tblTest1]
([CodeID],[TypeID],[Name],[Order],[GroupId])
VALUES(714211000006114,3,'MMR',1,4)
INSERT INTO [dbo].[tblTest1]
([CodeID],[TypeID],[Name],[Order],[GroupId])
VALUES(264268018,3,'NMR',2,4)
INSERT INTO [dbo].[tblTest1]
([CodeID],[TypeID],[Name],[Order],[GroupId])
VALUES(2244981000000119,7,'Rotavirus',1,5)
INSERT INTO [dbo].[tblTest1]
([CodeID],[TypeID],[Name],[Order],[GroupId])
VALUES(2245021000000115,7,'Rotavirus',2,5)
Code for expected result:
SELECTName,
[1] AS CodeId1,
[2] AS CodeId2,
[3] AS CodeId3,
[4] AS CodeId4,
[5] AS CodIde5
FROM(
SELECTName,
[Order],
CodeID
FROM[dbo].[tblTest1]
)AS up
PIVOT (SUM(CodeID) FOR [Order] IN ([1], [2], [3], [4], [5])) AS PVT
Output:
Name CodeId1 CodeId2 CodeId3 CodeId4 CodIde5
------------------------------ -------------------- -------------------- -------------------- -------------------- --------------------
dTaP/IPV NULL NULL NULL NULL 977661000006117
DTaP/IPV pre-school booster NULL NULL NULL 1540611000006110 NULL
DTaP/IPV/Hib 977621000006111 977631000006114 977641000006116 NULL NULL
Hib/Men C booster NULL NULL NULL 407721000000118 NULL
MenC 458773016 NULL NULL NULL NULL
MMR 714211000006114 NULL NULL NULL NULL
NMR NULL 264268018 NULL NULL NULL
PCV 405281000000118 405301000000117 405321000000114 NULL NULL
Rotavirus 2244981000000119 2245021000000115 NULL NULL NULL
-
Thanks for all the responses..
I get the data from 3 different tables. I used case when to get the result as needed. It works fine as expected.
My query is :
select CourseName,max(FirstCode),max(SecondCode),max(ThirdCode),CourseTypeId
from
(
select ct.CourseName,ct.CourseTypeId,
case when GroupOrder <>2 and GroupOrder <> 3 then si.CodeId end as FirstCode,
case when GroupOrder = 2 then si.CodeId end as SecondCode,
case when GroupOrder = 3 then si.CodeId end as ThirdCode
from VaccsAndImms.ScheduleItem si
inner join VaccsAndImms.ScheduleItemGroup sg
on si.ScheduleItemGroupId= sg.ScheduleItemGroupId
inner join VaccsAndImms.CourseType ct
on ct.CourseTypeId =si.CourseTypeId
)Result
group by CourseName,CourseTypeId
order by CourseTypeId
-
and your question is?
Anju Renjith (3/23/2015)
Thanks for all the responses..I get the data from 3 different tables. I used case when to get the result as needed. It works fine as expected.
My query is :
select CourseName,max(FirstCode),max(SecondCode),max(ThirdCode),CourseTypeId
from
(
select ct.CourseName,ct.CourseTypeId,
case when GroupOrder <>2 and GroupOrder <> 3 then si.CodeId end as FirstCode,
case when GroupOrder = 2 then si.CodeId end as SecondCode,
case when GroupOrder = 3 then si.CodeId end as ThirdCode
from VaccsAndImms.ScheduleItem si
inner join VaccsAndImms.ScheduleItemGroup sg
on si.ScheduleItemGroupId= sg.ScheduleItemGroupId
inner join VaccsAndImms.CourseType ct
on ct.CourseTypeId =si.CourseTypeId
)Result
group by CourseName,CourseTypeId
order by CourseTypeId
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply