Asking for 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

Asking for Query

Author

Topic

lw1990
Yak Posting Veteran

85 Posts

Posted - 2012-10-22 : 17:19:20

Hi,
I'm trying to create a procedure to create a list from Table1. In the finished list, each row containing only 2 fields, the first field has the same distinct records and the second field has the items listed in the different records in the Table1
The Table1 has the records like this:
Name State

Aaron NY
Aaron NJ
Aaron DC
Brain VA
Brain NY
Clint DC
Clint MA
Clint NJ
David NY

The result is going be like this:
Name State

Aaron NY, NJ, DC
Brain VA, NY
Clint DC, MA, NJ
David NY

Please show me any query.

Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-22 : 17:21:32

[code]
SELECT Name,
STUFF((SELECT ',' + State FROM Table1 WHERE Name = t.Name FOR XML PATH('')),1,1,'')
FROM (SELECT DISTINCT Name FROM Table1)t
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

lw1990
Yak Posting Veteran

85 Posts

Posted - 2012-10-22 : 22:14:18

quote:
Originally posted by visakh16

SELECT Name,
STUFF((SELECT ',' + State FROM Table1 WHERE Name = t.Name FOR XML PATH('')),1,1,'')
FROM (SELECT DISTINCT Name FROM Table1)t

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Thank you visakh16,

But, I'm using SQL Server2000, The FOR XML doesn't work in the Query Analyzer. Is there anything like subquery or local veryable I can use to query the table?

Thanks again,

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-10-23 : 01:42:51

CREATE TABLE StateTable(name varchar(10), [state] varchar(5))
INSERT INTO StateTable
SELECT 'Aaron', 'NY' UNION ALL
SELECT 'Aaron', 'NJ' UNION ALL
SELECT 'Aaron', 'DC' UNION ALL
SELECT 'Brain', 'VA' UNION ALL
SELECT 'Brain', 'NY' UNION ALL
SELECT 'Clint', 'DC' UNION ALL
SELECT 'Clint', 'MA' UNION ALL
SELECT 'Clint', 'NJ' UNION ALL
SELECT 'David', 'NY'

GO
IF OBJECT_ID('[dbo].[udf_CSVFormat]') IS NOT NULL DROP FUNCTION [dbo].[udf_CSVFormat]
GO
CREATE FUNCTION [dbo].[udf_CSVFormat]
(@name varchar(10))
RETURNS VARCHAR(500)
AS
BEGIN
declare @states varchar(500)
SELECT @states = COALESCE(@states + ', ', '') + [state]
FROM StateTable where Name=@name
RETURN (@states)
END
GO

SELECT distinct name, [dbo].[udf_CSVFormat]( name)
FROM StateTable

--
Chandu

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources