SELECT Column Side by Side - 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

SQL Server 2008 Forums

Transact-SQL (2008)

SELECT Column Side by Side

Author

Topic

besadmin
Posting Yak Master

116 Posts

Posted - 2012-09-19 : 16:01:34

Hey Friends,

I have what I hope will be a pretty simple question... I am stuggling with it though.

So I have a table Like this

ItemID Component
MS192 Bar
MS192 Chain
MS192 Ring

How do I query that table to get a result like the following

ItemI Com1 Com2 Com3
MS192 Bar Chain Ring

Any ideas?

Any help with this is greatly appreciate as always. Thanks in advance to anyone who can take the time to help me with this issue.

Thanks friends!!

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-19 : 17:24:53

I suspect there is more data and conditions that you need to satisfy than what you are showing in the sample data. In general, you would use PIVOTing to achieve this. For your sample data, the query would be as shown below.

If you have an unknown number of components, or if their names are not known in advance, you would need to modify the query - see the following links that may be helpful:
http://msdn.microsoft.com/en-us/library/ms177410(v=sql.105).aspx
http://beyondrelational.com/modules/2/blogs/70/posts/10840/dynamic-pivot-in-sql-server-2005.aspx

Also, if you are doing this for presenting in a client software (such as SSRS), more often than not, such client software (SSRS, for one) has much easier and better methods for pivoting.

SELECT
	ItemID AS ItemI,
	Bar AS Com1,
	Chain AS Com2,
	Ring AS Com3
FROM
	YourTable
PIVOT
(MAX(Component) FOR Component IN ([Bar],[Chain],[Ring]))p

Mike Jackson
Starting Member

37 Posts

Posted - 2012-09-20 : 15:24:30

Here is one I just did for someone else... Just plug in your fields

create table yourfile (Organizationid varchar(50),
Fieldvalue varchar(50))

Insert into yourfile values ('f15e61d8-10d8-49a2-a56f-005697d5cd4f','Hospital')
Insert into yourfile values ('f15e61d8-10d8-49a2-a56f-005697d5cd4f','Southeast')
Insert into yourfile values ('f15e61d8-10d8-49a2-a56f-005697d5cd4f','Transplant Center')

SELECT DISTINCT s1.[Organizationid],
STUFF(
(SELECT ', ' + s2.[Fieldvalue]
FROM yourfile AS s2
WHERE s2.[Organizationid] = s1.[Organizationid]
FOR XML PATH(''))
, 1, 1, '') AS Fieldvalue2
FROM yourfile AS s1
ORDER BY s1.[Organizationid]

Result....

f15e61d8-10d8-49a2-a56f-005697d5cd4f Hospital, Southeast, Transplant Center

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-20 : 23:28:39

quote:
Originally posted by Mike Jackson

Here is one I just did for someone else... Just plug in your fields

create table yourfile (Organizationid varchar(50),
Fieldvalue varchar(50))

Insert into yourfile values ('f15e61d8-10d8-49a2-a56f-005697d5cd4f','Hospital')
Insert into yourfile values ('f15e61d8-10d8-49a2-a56f-005697d5cd4f','Southeast')
Insert into yourfile values ('f15e61d8-10d8-49a2-a56f-005697d5cd4f','Transplant Center')

SELECT DISTINCT s1.[Organizationid],
STUFF(
(SELECT ', ' + s2.[Fieldvalue]
FROM yourfile AS s2
WHERE s2.[Organizationid] = s1.[Organizationid]
FOR XML PATH(''))
, 1, 1, '') AS Fieldvalue2
FROM yourfile AS s1
ORDER BY s1.[Organizationid]

Result....

f15e61d8-10d8-49a2-a56f-005697d5cd4f Hospital, Southeast, Transplant Center

I think OP is expecting values to come in different columns rather than coming as comma separated

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources