Group By - Solved - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Group By - Solved

Author

Topic

Quentin
Starting Member

12 Posts

Posted - 2011-12-18 : 21:04:56

T-SQL
I need to group my results but I am having trouble understanding how to do it in this scenario.

I have a table with the following

sampleid, name, value

data is like this
sampleid, name, value
123, test1, 1234
123, test2, 9876
123, test3, 5564
etc

I would like to display the results as this

samplied, test1, test2, test3
123, 1234, 9876, 5564

I use the following code

SELECT SAMPLEID,
CASE WHEN NAME = 'test1' THEN VALUE END AS TEST1
CASE WHEN NAME = 'test2' THEN VALUE END AS TEST2
CASE WHEN NAME = 'test3' THEN VALUE END AS TEST3
FROM TESTTABLE

I then want to add GROUP BY SAMPLEID but this is obviously incorrect as I need ALL the column names in the group by.

I could use the MIN command but I need ALL the results.

Is there a JOIN or UNION I can use to get the layout I need.

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-12-18 : 21:17:51

[code]
SELECT SAMPLEID,
MIN(CASE WHEN NAME = 'test1' THEN VALUE END) AS TEST1
MIN(CASE WHEN NAME = 'test2' THEN VALUE END) AS TEST2
MIN(CASE WHEN NAME = 'test3' THEN VALUE END) AS TEST3
FROM TESTTABLE
GROUP BY SAMPLEID
[/code]

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-12-18 : 21:18:28

Also if you are using SQL 2005 / 2008, check out the PIVOT operator

KH
[spoiler]Time is always against us[/spoiler]

Quentin
Starting Member

12 Posts

Posted - 2011-12-18 : 21:57:14

Thanks khtan, I had a look at PIVOT but it represents the same problem. To clarify, I have an additional column i forgot to mention

so the layout goes like this

sampleid, duplicate, name, value
123, PRIMARY, test1, 1234
123, PRIMARY, test2, 9876
123, PRIMARY, test3, 5564
123, LR1, test1, 1234
123, LR1, test2, 9876
123, LR1, test3, 5564
etc

I therefore, cannot use min as I need all the results.
If possible, the results should be like this

Sampleid, Primary - test1,Primary - test2,Primary - test3, LR-test3, LR-test3, LR-test3
123, 1234, 9876, 5564, 1234, 9876, 5564

Hope that clarifies it a bit better

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-12-18 : 22:06:22

[code]
SELECT SAMPLEID,
MIN(CASE WHEN duplicate = 'PRIMARY' and NAME = 'test1' THEN VALUE END) AS Primary_TEST1,
MIN(CASE WHEN duplicate = 'PRIMARY' and NAME = 'test2' THEN VALUE END) AS Primary_TEST2,
MIN(CASE WHEN duplicate = 'PRIMARY' and NAME = 'test3' THEN VALUE END) AS Primary_TEST3,
MIN(CASE WHEN duplicate = 'LR1' and NAME = 'test1' THEN VALUE END) AS LR_TEST1,
. . .
FROM TESTTABLE
GROUP BY SAMPLEID
[/code]

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-12-18 : 22:09:40

the PIVOT way


select	*
from	(
		select	sampleid, dup_name = duplicate + '-' + name, value
		from	TESTTABLE
	) d
	pivot
	(
		min(value)
		for	dup_name in ([PRIMARY-test1], [PRIMARY-test2], [PRIMARY-test3], [LR1-test1], [LR1-test2], [LR1-test3])
	) p

KH
[spoiler]Time is always against us[/spoiler]

Quentin
Starting Member

12 Posts

Posted - 2011-12-19 : 00:06:27

Thanks khtan,

That worked fine.
Appreciate your assistance

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-12-19 : 00:12:20

welcome

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources