SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

chbala85
Starting Member

47 Posts

Posted - 07/18/2013 :  01:42:17  Show Profile  Reply with Quote
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

India
52249 Posts

Posted - 07/18/2013 :  01:46:52  Show Profile  Reply with Quote

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


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

Edited by - visakh16 on 07/18/2013 01:47:27
Go to Top of Page

mohan123
Constraint Violating Yak Guru

India
252 Posts

Posted - 07/18/2013 :  06:42:33  Show Profile  Reply with Quote
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

Edited by - mohan123 on 07/18/2013 06:48:39
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000