Data arrangement - columns to rows in a specific way

Question

Post reply

Data arrangement - columns to rows in a specific way

Anju Renjith

Ten Centuries

Points: 1135
More actions
March 23, 2015 at 4:46 am

#398222

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

DaPainKiller SSChasing Mays Points: 611 More actions · Answer 1

DaPainKiller

SSChasing Mays

Points: 611

March 23, 2015 at 4:56 am

#1785651

Just to let you know that the image is not showing here on the thread.

Anju Renjith Ten Centuries Points: 1135 More actions · Answer 2

CodeID TypeID Name Order GroupId

977621000006111 1 DTaP/IPV/Hib 1 1

977631000006114 1 DTaP/IPV/Hib 2 1

977641000006116 1 DTaP/IPV/Hib 3 1

1540611000006110 6 DTaP/IPV pre-school booster 4 1

977661000006117 8 dTaP/IPV 5 1

405281000000118 2 PCV 1 2

405301000000117 2 PCV 2 2

405321000000114 2 PCV 3 2

458773016 4 MenC 1 3

407721000000118 5 Hib/Men C booster 4 3

714211000006114 3 MMR 1 4

264268018 3 MMR 2 4

2244981000000119 7 Rotavirus 1 5

2245021000000115 7 Rotavirus 2 5

Output must be:

Name CodeId CodeId CodeId

DTaP/IPV/Hib 977621000006111 977631000006114 977641000006116

PCV 405281000000118 405301000000117 405321000000114

MenC 458773016 407721000000118 null

MMR 714211000006114 264268018 null

Rotavirus 2244981000000119 2245021000000115 null

DTaP/IPV pre-school booster 1540611000006110 null null

Hib/Men C booster 407721000000118 null null

Anju Renjith Ten Centuries Points: 1135 More actions · Answer 3

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

Anju Renjith (3/23/2015)

CodeID TypeID Name Order GroupId

977621000006111 1 DTaP/IPV/Hib 1 1

977631000006114 1 DTaP/IPV/Hib 2 1

977641000006116 1 DTaP/IPV/Hib 3 1

1540611000006110 6 DTaP/IPV pre-school booster 4 1

977661000006117 8 dTaP/IPV 5 1

405281000000118 2 PCV 1 2

405301000000117 2 PCV 2 2

405321000000114 2 PCV 3 2

458773016 4 MenC 1 3

407721000000118 5 Hib/Men C booster 4 3

714211000006114 3 MMR 1 4

264268018 3 MMR 2 4

2244981000000119 7 Rotavirus 1 5

2245021000000115 7 Rotavirus 2 5

Output must be:

Name CodeId CodeId CodeId

DTaP/IPV/Hib 977621000006111 977631000006114 977641000006116

PCV 405281000000118 405301000000117 405321000000114

MenC 458773016 407721000000118 null

MMR 714211000006114 264268018 null

Rotavirus 2244981000000119 2245021000000115 null

DTaP/IPV pre-school booster 1540611000006110 null null

Hib/Men C booster 407721000000118 null 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 Shaw}

For 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

DaPainKiller SSChasing Mays Points: 611 More actions · Answer 5

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:



SELECT		Name,

			[1] AS CodeId1,

			[2] AS CodeId2,

			[3] AS CodeId3,

			[4] AS CodeId4,

			[5] AS CodIde5

FROM		(

				SELECT	Name,

						[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

Anju Renjith Ten Centuries Points: 1135 More actions · Answer 6

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

DaPainKiller SSChasing Mays Points: 611 More actions · Answer 7

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