Query Help. - 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 2005 Forums

Transact-SQL (2005)

Query Help.

Author

Topic

NguyenL71
Posting Yak Master

228 Posts

Posted - 2009-11-25 : 12:16:40

How can I convert column data to single row base on licenseid. Please see desire results below.
Thank you in advance.

--SQL 2005

IF OBJECT_ID('Tempdb.dbo.#Temp', 'u') IS NOT NULL
DROP TABLE #Temp
GO

CREATE TABLE #Temp
(
LicenseId INT NOT NULL,
StateCd CHAR(2) NULL,
);
GO

INSERT #Temp VALUES (2, 'AZ');
INSERT #Temp VALUES (2, 'CA');
INSERT #Temp VALUES (2, 'NE');
INSERT #Temp VALUES (2, 'NM');
INSERT #Temp VALUES (2, 'NY');

INSERT #Temp VALUES (3, 'KS');
INSERT #Temp VALUES (3, 'CA');
INSERT #Temp VALUES (3, 'AZ');

INSERT #Temp VALUES (4, 'ID');
INSERT #Temp VALUES (4, 'GA');

INSERT #Temp VALUES (5, 'CO');
INSERT #Temp VALUES (5, 'TX');
INSERT #Temp VALUES (5, 'VA');
INSERT #Temp VALUES (5, 'OR');
INSERT #Temp VALUES (5, 'WA');
INSERT #Temp VALUES (5, 'SC');
GO

--Desire results:

SELECT *
FROM #Temp;
go

LicenseId StateCd
---------- ------------------
2 AZ, CA, NE, NM, NY
3 KS, CA, AZ
4 ID, GA
5 CO, TX, VA, OR, WA, SC

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-11-25 : 12:21:42

[code]select LicenseId,
StateCd = STUFF((SELECT TOP 100 PERCENT ',' + x.StateCd FROM #Temp x
WHERE x.LicenseId = y.LicenseId
ORDER BY ',' + x.StateCd FOR XML PATH('')), 1, 1, '')
from #Temp y
group by LicenseId[/code]

NguyenL71
Posting Yak Master

228 Posts

Posted - 2009-11-25 : 12:28:46

quote:

Originally posted by vijayisonly

select	LicenseId, 
	StateCd	= STUFF((SELECT TOP 100 PERCENT ',' + x.StateCd FROM #Temp x 
				WHERE 	x.LicenseId 	= y.LicenseId 
				ORDER BY ',' + x.StateCd FOR XML PATH('')), 1, 1, '')
from	#Temp y
group by LicenseId

Work great. I appreciate your help.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-11-25 : 12:32:03

Np... ur welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources