how to change multiple records in one record

Question

how to change multiple records in one record

bauke.dijkstra

SSC Enthusiast

Points: 127
More actions
April 14, 2015 at 5:02 am

#295982

hi,
i have a query i.e.
select project, description from table
with results
chocolate | white
chocolate | black
chocolate | brown
sugar | black
vinegar | yellow
i would like to get a result like:
chocolate | white, blakc, brown
sugar | black
vinegar | yellow
so I would like to get all values of one project in one records included with separators/a space in it

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

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

serg-52 SSCrazy Eights Points: 9802 More actions · Answer 1

Use FOR XML PATH('') .See http://stackoverflow.com/questions/4944680/flatten-sql-server-table-to-a-string"> http://stackoverflow.com/questions/4944680/flatten-sql-server-table-to-a-string

for example.

Megha P SSCommitted Points: 1976 More actions · Answer 2

Create table test

(project varchar(50),

descripation varchar(50)

)

Insert test

SELECT 'chocolate' ,'white'

UNION ALL

SELECT 'chocolate' ,' black'

UNION ALL

SELECT 'chocolate' ,' brown'

UNION ALL

SELECT 'sugar' ,' black'

UNION ALL

SELECT 'vinegar' ,'yellow'

SELECT DISTINCT project,

STUFF( (SELECT ',' + descripation FROM test WHERE Test.project = T.project FOR XML PATH('')) ,1,2,'')

FROM test T

bauke.dijkstra SSC Enthusiast Points: 127 More actions · Answer 3

i am getting the error

`the multipart identifier project could not be found`

djj SSCoach Points: 18831 More actions · Answer 4

bauke.dijkstra (4/14/2015)
i am getting the error
`the multipart identifier project could not be found`

Without your code it is hard to give an answer.

bauke.dijkstra SSC Enthusiast Points: 127 More actions · Answer 5

Create table bstest

(project varchar(50),

description varchar(50)

)

Insert bstest (project, description)

(select project, description from table)

** insert 19 rows **

SELECT DISTINCT project,

STUFF( (SELECT ',' + description FROM bstest WHERE bstest.project = T.project FOR XML PATH('')) ,1,2,'')

FROM bstest T

djj SSCoach Points: 18831 More actions · Answer 6

bauke.dijkstra (4/14/2015)
Create table bstest
(project varchar(50),
description varchar(50)
)
Insert bstest (project, description)
(select project, description from table)
** insert 19 rows **
SELECT DISTINCT project,
STUFF( (SELECT ',' + description FROM bstest WHERE bstest.project = T.project FOR XML PATH('')) ,1,2,'')
FROM bstest T

So you have a table named "table"? (select project, description from table)

bauke.dijkstra SSC Enthusiast Points: 127 More actions · Answer 7

i think i changed something. now it is working thanks

spaghettidba SSC Guru Points: 105712 More actions · Answer 8

spaghettidba

SSC Guru

Points: 105712

April 14, 2015 at 5:53 am

#1790750

Removed

-- Gianluca Sartori