Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

A Pivot, but not exactly. Expand / Collapse
Author
Message
Posted Thursday, May 2, 2013 9:43 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 11:19 AM
Points: 567, Visits: 472
I have a table that could look like the following:

ID FK_ID Value
1 100 'Blue'
2 100 'Black'
3 100 'Green'
4 101 'Blue'
5 101 'Green'
6 102 'Black'

I need a query that outputs the following:

FK_ID NewVal
100 'Blue,Black,Green'
101 'Blue, Green'
102 'Black'

Also, I won't always know that 'Blue', 'Black', or 'Green' are going to be the distinct values.
Post #1448867
Posted Thursday, May 2, 2013 9:56 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 6:47 PM
Points: 23,396, Visits: 32,229
Use something like this:


with basedata as (
select distinct
FK_ID
from
dbo.MyTable
)
select
FK_ID,
NewVal = stuff((select ',' + Value
from dbo.MyTable mt
where mt.FK_ID = bd.FK_ID
order by mt.ID
for xml path(''),TYPE).value('.','varchar(max)'),1,1,'')
from
basedata bd;





Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1448878
Posted Thursday, May 2, 2013 9:59 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 11:19 AM
Points: 567, Visits: 472
Interesting. Thanks!
Post #1448881
Posted Thursday, May 2, 2013 3:36 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, March 6, 2014 12:39 PM
Points: 376, Visits: 172
the method above is useful since its dynamic, but i wanted to show you a more manual way of doing it as well.



CREATE taBLE #TEMP
(
id INT,
fk_id INT,
value VARCHAR(10)


)
INSERT INTO #TEMP
VALUES(1, 100, 'Blue'),
(2, 100, 'Black'),
(3, 100, 'Green'),
(4, 101, 'Blue'),
(5, 101, 'Green'),
(6, 102, 'Black')

SELECT FK_ID, REPLACE(REPLACE(RTRIM(LTRIM(COALESCE(Blue ,'') +' '+ COALESCE(Black,'') +' '+ COALESCE(Green ,''))),' ',','),',,',',')AS VALUESS

FROM(
select fk_id, MIN(case when value = 'Blue' then 'Blue' END) Blue,MIN(case when value = 'Black' then 'Black' END) black, MIN(case when value = 'Green' then 'Green' END)Green
from #TEMP
group by fk_id)I
Post #1448997
Posted Thursday, May 2, 2013 3:41 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 11:19 AM
Points: 567, Visits: 472
Thanks, but like I mentioned - "I won't always know that 'Blue', 'Black', or 'Green' are going to be the distinct values."
Post #1448999
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse