From Rows to One Column 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

General SQL Server Forums

New to SQL Server Programming

From Rows to One Column Help

Author

Topic

conedm
Starting Member

33 Posts

Posted - 2010-09-02 : 10:34:58

I am trying to take multiple rows and comma seperate one column with the data. It is not doing it. Similar statements work but not this one. Can some one help? Below is the code...
SELECT dbo.WEBVENDOR.VENDORID, STUFF
((SELECT ',' + CONVERT(VARCHAR(6), CSCONTRACTS.dbo.CONTRPROPOSALS.SCALENUM)
FROM CSCONTRACTS.dbo.CONTRPROPOSALS
WHERE WEBVENDOR.SCALENUM = CSCONTRACTS.dbo.CONTRPROPOSALS.SCALENUM AND CSCONTRACTS.dbo.CONTRPROPOSALS.SEQUENCE = 2 FOR XML PATH('')), 1, 1, '') AS [SCALES]
FROM dbo.WEBVENDOR
WHERE (WEBVENDOR.SEQUENCE = 1 OR WEBVENDOR.SEQUENCE = 11)
ORDER BY WEBVENDOR.VENDORID

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-09-02 : 12:27:26

Did you get any error or unexpected result?

Madhivanan

Failing to plan is Planning to fail

conedm
Starting Member

33 Posts

Posted - 2010-09-02 : 12:36:52

I always get this error:
Error in WHERE clause near '('. Unable to parse query text.
But I get that even with the similar statements that work.

the result set shows everything as a seperate record
example
vendorid scales
408 1543
408 1480
408 4081
I want:
vendorid scales
408 1543,1480,4081

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-09-02 : 12:42:12

Are you using SQL Server version higher than 2000?

Madhivanan

Failing to plan is Planning to fail

conedm
Starting Member

33 Posts

Posted - 2010-09-02 : 12:44:16

Sorry. I forgot to mention. I am using SQL Server 2005

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-09-02 : 12:59:09

[code]

declare @t table (vendorid int,scales int)
insert into @t
select 408 ,1543 union all
select 408, 1480 union all
select 408, 4081

select * from @t
SELECT DISTINCT s1.vendorid,
STUFF((SELECT DISTINCT TOP 100 PERCENT ',' + cast(s2.scales as varchar(10)) FROM @t AS s2 WHERE s2.vendorid = s1.vendorid ORDER BY ',' + cast(s2.scales as varchar(10)) FOR XML PATH('')), 1, 1, '') AS scales
FROM @t AS s1
ORDER BY s1.vendorid

[/code]

Madhivanan

Failing to plan is Planning to fail

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2010-09-02 : 13:05:41

Please, provide sample data and expected output

conedm
Starting Member

33 Posts

Posted - 2010-09-02 : 15:40:41

I used what you had and had to alter it a bit to work. But now it is perfect.
Thank you!!!
Here is what it ended up being
DECLARE @t TABLE (vendorid int, scales int) INSERT INTO @t
SELECT WEBVENDOR.VENDORID, WEBVENDOR.SCALENUM
FROM WEBVENDOR INNER JOIN
CSCONTRACTS.dbo.CONTRPROPOSALS ON
WEBVENDOR.SCALENUM = CSCONTRACTS.dbo.CONTRPROPOSALS.SCALENUM
WHERE (WEBVENDOR.SEQUENCE = 11 OR
WEBVENDOR.SEQUENCE = 1) AND (CSCONTRACTS.dbo.CONTRPROPOSALS.SEQUENCE = 2)
SELECT DISTINCT s1.vendorid, STUFF
((SELECT DISTINCT ',' + CONVERT(varchar(10), s2.scales)
FROM @t AS s2
WHERE s2.vendorid = s1.vendorid
ORDER BY ',' + CONVERT(varchar(10), s2.scales) FOR XML PATH('')), 1, 1, '') AS scales
FROM @t AS s1
ORDER BY s1.vendorid

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-09-03 : 04:15:53

quote:
Originally posted by conedm

I used what you had and had to alter it a bit to work. But now it is perfect.
Thank you!!!
Here is what it ended up being
DECLARE @t TABLE (vendorid int, scales int) INSERT INTO @t
SELECT WEBVENDOR.VENDORID, WEBVENDOR.SCALENUM
FROM WEBVENDOR INNER JOIN
CSCONTRACTS.dbo.CONTRPROPOSALS ON
WEBVENDOR.SCALENUM = CSCONTRACTS.dbo.CONTRPROPOSALS.SCALENUM
WHERE (WEBVENDOR.SEQUENCE = 11 OR
WEBVENDOR.SEQUENCE = 1) AND (CSCONTRACTS.dbo.CONTRPROPOSALS.SEQUENCE = 2)
SELECT DISTINCT s1.vendorid, STUFF
((SELECT DISTINCT ',' + CONVERT(varchar(10), s2.scales)
FROM @t AS s2
WHERE s2.vendorid = s1.vendorid
ORDER BY ',' + CONVERT(varchar(10), s2.scales) FOR XML PATH('')), 1, 1, '') AS scales
FROM @t AS s1
ORDER BY s1.vendorid

You are welcome

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources