get data from query in single row

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

get data from query in single row

Author

Topic

kneel
Starting Member

36 Posts

Posted - 2008-06-23 : 04:45:38

Hello All,

I have following type of table with data.

Year id value
01-01-2008 123 10
01-01-2008 131 11
01-01-2008 139 132
01-01-2007 153 112
01-01-2007 154 134
01-01-2007 155 164

I want to write query such that I should get value of particular year in single row as follows

Date Col 1 Col 2 Col 3
01-01-2008 10 11 132
01-01-2007 112 134 164

How can I write this type of query ? Please do let me know if anyone know how to write it ?

Thanks in advance.

--kneel

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-23 : 04:48:02

It's call PIVOT or CROSSTAB report.
Are you using SQL Server 2000 or SQL Server 2005?

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-23 : 04:48:30

Can there be any number of columns for a particular date?

E 12°55'05.25"
N 56°04'39.16"

kneel
Starting Member

36 Posts

Posted - 2008-06-23 : 04:53:14

i am using SQL server 2005. At max there can be 13 columns for particular date.

--kneel

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-23 : 05:00:47

[code]DECLARE @Sample TABLE ([Year] DATETIME, ID INT, [Value] INT)

SET DATEFORMAT MDY

INSERT @Sample
SELECT '01-01-2008', 123, 10 UNION ALL
SELECT '01-01-2008', 131, 11 UNION ALL
SELECT '01-01-2008', 139, 132 UNION ALL
SELECT '01-01-2007', 153, 112 UNION ALL
SELECT '01-01-2007', 154, 134 UNION ALL
SELECT '01-01-2007', 155, 164

SELECT p.[Year],
p.[1],
p.[2],
p.[3],
p.[4],
p.[5],
p.[6],
p.[7],
p.

,
p.[9],
p.[10],
p.[11],
p.[12],
p.[13]
FROM (
SELECT [Year],
ROW_NUMBER() OVER (PARTITION BY [Year] ORDER BY ID) AS RecID,
[Value]
FROM @Sample
) AS s
PIVOT (
MAX([Value])
FOR RecID IN ([1], [2], [3], [4], [5], [6], [7],

, [9], [10], [11], [12], [13])
) AS p
ORDER BY p.[Year] DESC[/code]

E 12°55'05.25"
N 56°04'39.16"

kneel
Starting Member

36 Posts

Posted - 2008-06-23 : 05:49:48

tons of thanks it works fine.... :)

--kneel

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources