Query - 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

Query

Author

Topic

chbala85
Starting Member

49 Posts

Posted - 2013-07-18 : 01:42:17

Hi,

id PnId result
157 10780 111-111-1111
157 10783 city
157 10782 dfgdfg
157 10784 state
158 10780 222-111-1111
158 10783 city
158 10782 hhhh
158 10784 state

i need result like "157" "111-111-1111,city,dfgdfg,state"
"158" "222-111-1111,city,hhhh,state"

Thanks,
krishna

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-18 : 01:46:52

[code]
SELECT '"' + CAST(id AS varchar(5)) + '"',
'"' + STUFF((SELECT ',' + [result]
FROM Table
WHERE id = t.id
ORDER BY PnId
FOR XML PATH('')),1,1,'') + '"'
FROM (SELECT DISTINCT id FROM table) t
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

mohan123
Constraint Violating Yak Guru

252 Posts

Posted - 2013-07-18 : 06:42:33

declare @sample table (id int, status varchar(30))

insert @sample
select 157 , '111-111-1111' union all
select 157 , 'city' union all
select 157 , 'dfgdfg' union all
select 157 , 'state' union all
select 158 , '222-111-1111 ' union all
select 158, 'hhh' union all
select 158, 'ttt' union all
select 158, 'lll' union all
select 158, 'ffsasa'

SELECT distinct   QUOTENAME(CAST( id  As VARCHAR(5)),'"'),
 QUOTENAME(STUFF((
			SELECT ',' + LTRIM(RTRIM(STATUS))
			FROM @sample s
			WHERE s.id = L.ID
			ORDER BY l.id
			FOR XML PATH('')), 1,
				1,
				''
			),'"')	
FROM @sample l

P.V.P.MOhan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources