View data in 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

View data in query

Author

Topic

issam
Starting Member

30 Posts

Posted - 2008-04-09 : 09:08:52

Hi,
I have an accounting table with 5 year of data for an account, the table headers look as following

Acctno___Year___db_1,db_2,db,db_3,db4,….db_12,Cr_1,cr_2,cr_3…,cr-12

I want to with select statement to convert the data to query the header must be the following:-

Years1___Year2___year3___year4____year5
Db_1
Db_2
Db_3
.
.
.

Thanks for any help

pravin14u
Posting Yak Master

246 Posts

Posted - 2008-04-09 : 09:10:07

use PIVOT?

Prakash.P
The secret to creativity is knowing how to hide your sources!

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-04-09 : 09:12:35

more likely UNPIVOT, though i'm really unclear on the columns etc from the OP's question

can you try and post again using code tags to make it clearer?

Em

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-09 : 09:28:32

[code]DECLARE @Sample TABLE (Acctno INT, Year INT, db_1 VARCHAR(10), db_2 VARCHAR(10), db_3 VARCHAR(10), Cr_1 INT, cr_2 INT, cr_3 INT)

INSERT @Sample
SELECT 1, 2007, 'db1', 'db2', 'db3', 17001, 17002, 17003 UNION ALL
SELECT 1, 2008, 'db1', 'db2', 'db3', 18001, 18002, 18003 UNION ALL
SELECT 2, 2008, 'db1', 'db2', NULL, 28001, 28002, 28003

SELECT q.DbName,
q.[1] AS Year1,
q.[2] AS Year2,
q.[3] AS Year3,
q.[4] AS Year4,
q.[5] AS Year5
FROM (
SELECT p.Year,
p.DbName,
ROW_NUMBER() OVER (PARTITION BY p.DbName ORDER BY p.Year) AS ColID
FROM @Sample AS s
UNPIVOT (
DbName
FOR DbCol IN (s.db_1, s.db_2, s.db_3)
) AS p
) AS s
PIVOT (
MAX(s.Year)
FOR s.ColID IN ([1], [2], [3], [4], [5])
) AS q[/code]

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources