How to split/select data of one column into multip

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)

How to split/select data of one column into multip

Author

Topic

dnpavankumar
Starting Member

1 Post

Posted - 2009-04-11 : 16:42:42

Hi,
Good morning. This post is regarding an SQL query to select data from a table.

Table structure as follows:
----------------------------
CREATE TABLE T1 (ID INT, CUST_ID INT, VAL VARCHAR(255))

INSERT INTO T1 VALUES(1, 100, 'A')
INSERT INTO T1 VALUES(2, 100, 'B')
INSERT INTO T1 VALUES(3, 100, 'C')

INSERT INTO T1 VALUES(1, 101, 'D')
INSERT INTO T1 VALUES(2, 101, 'E')
INSERT INTO T1 VALUES(3, 101, 'F')

INSERT INTO T1 VALUES(1, 102, 'X')
INSERT INTO T1 VALUES(2, 102, 'Y')
INSERT INTO T1 VALUES(3, 102, 'Z')
----------------------------

How can i write an SQL query to get the result as follows:

ID------CUST_ID_100------------CUST_ID_101--------------CUST_ID_102
1-------A-----------------------D-----------------------X
2-------B-----------------------E-----------------------Y
3-------C-----------------------F-----------------------Z

I used (----) symbols only to format the data in this post. They are not part of the result of query.

Thanks in advance.

Best Regards,
Pavan Kumar.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-12 : 13:13:20

[code]SELECT ID,
MAX(CASE WHEN CUST_ID = 100 THEN VAL ELSE NULL END) AS CUST_ID_100,
MAX(CASE WHEN CUST_ID = 101 THEN VAL ELSE NULL END) AS CUST_ID_101,
MAX(CASE WHEN CUST_ID = 102 THEN VAL ELSE NULL END) AS CUST_ID_102
FROM Table
GROUP BY ID
[/code]

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-04-12 : 23:50:36

Hi U can use Pivot Also, Try this once

SELECT ID,[100] AS CUST_ID_100,[101] AS CUST_ID_101,[102] AS CUST_ID_102
FROM ( SELECT * FROM @T1) AS P
PIVOT ( MAX(VAL) FOR CUST_ID IN ( [100],[101],[102])) AS PVT

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-13 : 02:48:15

For dynamic pivot
http://sqlblogcasts.com/blogs/madhivanan/archive/tags/dynamic+crosstab/default.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources