column to row - 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

Transact-SQL (2000)

column to row

Author

Topic

jung1975
Aged Yak Warrior

503 Posts

Posted - 2005-09-22 : 15:24:38

I am trying to convert table format


v_id     dx1     dx2   dx3   dx4
1001   205     401   403   404
1002   205     401   402
1003   205     401
1004   205


  num   v_id	DX
   1      1001   205
   2      1001   401
   3      1001   403
   4      1001   404
   1      1002   205
   2      1002   401
   3      1002   402
   1      1003   205
   2      1003   401
   1      1004   205

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2005-09-22 : 16:46:24

SELECT COUNT(*) AS num, A.v_id, A.dx1 AS DX FROM
(SELECT v_id, dx1 FROM YourTable WHERE dx1 IS NOT NULL
UNION
SELECT v_id, dx2 FROM YourTable WHERE dx2 IS NOT NULL
UNION
SELECT v_id, dx3 FROM YourTable WHERE dx3 IS NOT NULL
UNION
SELECT v_id, dx4 FROM YourTable WHERE dx4 IS NOT NULL) AS A
JOIN
(SELECT v_id, dx1 FROM YourTable WHERE dx1 IS NOT NULL
UNION
SELECT v_id, dx2 FROM YourTable WHERE dx2 IS NOT NULL
UNION
SELECT v_id, dx3 FROM YourTable WHERE dx3 IS NOT NULL
UNION
SELECT v_id, dx4 FROM YourTable WHERE dx4 IS NOT NULL) AS B
ON A.v_id = B.v_id AND A.dx1 >= B.dx1
GROUP BY A.v_id, A.dx1
ORDER BY A.v_id, A.dx1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources