Rows as columns - 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 2000 Forums

SQL Server Development (2000)

Rows as columns

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-03-22 : 08:46:01

Ashok writes "I have table like :
ROWID DID1 DCNT1 DID2 DCNT2 DID3 DCNT3
==============================================
1 101 10 102 5 103 3
2 102 5 103 6 104 2
3 101 6 103 6 104 5

I need the output like :
ROWID DID DNCT
=======================
1 101 10
1 102 5
1 103 3
2 102 5
2 103 6
2 104 2
3 101 6
3 103 6
3 104 5

Any help will be appericiated.

Thanks"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-03-22 : 08:48:49

Seems the table should be normalised
Refer this
http://www.datamodel.org/NormalizationRules.html

Also refer
http://www.sqlteam.com/item.asp?ItemID=2652

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-22 : 16:52:35

or use union all

select ROWID, DID1 as DID, DNCT1 as DNCT
from   table
union all
select ROWID, DID2 as DID, DNCT2 as DNCT
from   table
union all
select ROWID, DID3 as DID, DNCT3 as DNCT
from   table

KH
Choice is an illusion, created between those with power, and those without.
Concordantly, while your first question may be the most pertinent, you may or may not realize it is also the most irrelevant

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources