Coloumns to Rows - 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)

Coloumns to Rows

Author

Topic

goodman2253
Yak Posting Veteran

88 Posts

Posted - 2011-12-27 : 02:28:36

Hello All,
I am implementing my task and i am getting an issue so i want your help.
what i want to do is, suppose a table name is t1 which has two columns id and day.

id day
A 0
A 2
B 1
A 3
B 0
A 4

I want to write a query which should give output-:
A 0,2,3,4
B 1,0

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-12-27 : 02:30:47

see http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254

KH
[spoiler]Time is always against us[/spoiler]

goodman2253
Yak Posting Veteran

88 Posts

Posted - 2011-12-27 : 02:37:47

can't we use the concept of pivot in this case

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-12-27 : 02:52:08

pivot is different. PIVOTing will result in one column for each of the day. What you describe here is CSV of the value as one column

KH
[spoiler]Time is always against us[/spoiler]

goodman2253
Yak Posting Veteran

88 Posts

Posted - 2011-12-27 : 03:01:07

any other way we can do

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-12-27 : 03:03:11

see http://sqlblog.com/blogs/adam_machanic/archive/2006/07/12/rowset-string-concatenation-which-method-is-best.aspx

KH
[spoiler]Time is always against us[/spoiler]

goodman2253
Yak Posting Veteran

88 Posts

Posted - 2011-12-27 : 03:10:39

its the same thing.I am asking any other way except using stuff,and Xml.
actually i didn't understand both the terms

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-12-27 : 03:25:56

the last link that i posted uses scalar UDF method

KH
[spoiler]Time is always against us[/spoiler]

goodman2253
Yak Posting Veteran

88 Posts

Posted - 2011-12-27 : 03:33:13

thats correct. if you see my table the id is of varchar type.
so when i am executing it is giving conversion error

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-12-27 : 03:43:52

can you show us that query ?

KH
[spoiler]Time is always against us[/spoiler]

goodman2253
Yak Posting Veteran

88 Posts

Posted - 2011-12-27 : 04:16:52

SELECT DISTINCT s1.ID,
STUFF((SELECT TOP 100 PERCENT ',' + s2.days FROM T1 AS s2 WHERE s2.ID = s1.ID ORDER BY ',' + s2.days FOR XML PATH('')), 1, 1, '') AS days
FROM t1 AS s1
ORDER BY s1.ID

goodman2253
Yak Posting Veteran

88 Posts

Posted - 2011-12-27 : 04:17:22

and the error is-:
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value ',' to data type int.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-12-27 : 04:34:22

you will need to convert to string before concatenate


SELECT DISTINCT s1.ID,
STUFF((SELECT TOP 100 PERCENT ',' + convert(varchar(10), s2.days) FROM T1 AS s2 WHERE s2.ID = s1.ID ORDER BY ',' + s2.days FOR XML PATH('')), 1, 1, '') AS days
FROM t1 AS s1
ORDER BY s1.ID

KH
[spoiler]Time is always against us[/spoiler]

goodman2253
Yak Posting Veteran

88 Posts

Posted - 2011-12-27 : 05:12:42

HI the same error continues

goodman2253
Yak Posting Veteran

88 Posts

Posted - 2011-12-27 : 05:15:34

SELECT DISTINCT s1.ID,
STUFF((SELECT TOP 100 PERCENT ','+convert(varchar(2),s2.days) FROM t1 AS s2 WHERE s2.ID = s1.ID ORDER BY ',' + convert(varchar(2),s2.days) FOR XML PATH('')), 1, 1, '') AS days
FROM t1 AS s1
ORDER BY s1.ID

goodman2253
Yak Posting Veteran

88 Posts

Posted - 2011-12-27 : 05:16:17

thanks for your help.
without you it might not be possible

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-12-27 : 05:23:00

ya. i missed out the ORDER BY part

KH
[spoiler]Time is always against us[/spoiler]

goodman2253
Yak Posting Veteran

88 Posts

Posted - 2011-12-27 : 05:27:22

can you please explain me one thing
what is XML PATH.
and why we have used this in our query.

goodman2253
Yak Posting Veteran

88 Posts

Posted - 2011-12-28 : 03:26:27

can anyone please explain that we can use some thing different rather than XML path

goodman2253
Yak Posting Veteran

88 Posts

Posted - 2011-12-28 : 03:27:54

ya i implemented the scalar UDF method its working however its something complicated

goodman2253
Yak Posting Veteran

88 Posts

Posted - 2011-12-28 : 03:29:01

USE pubs
GO

CREATE FUNCTION dbo.ConcatAuthors(@State CHAR(2))
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @Output VARCHAR(8000)
SET @Output = ''

SELECT @Output = CASE @Output
WHEN '' THEN au_lname
ELSE @Output + ', ' + au_lname
END
FROM Authors
WHERE State = @State
ORDER BY au_lname

RETURN @Output
END
GO

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources