SQL solution

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

bangla2@yahoo.com - 13 Mar 2008 11:28 GMT

Here is a table where column2 needs to be transposed. The output table
will be id values in C2 transposed. The values in C2 can have
different values separated by commas. Is it possible? Thanks.

C1 C2
3 1,4,10,7
4 1,2,13,4,15,6,7,11,5,3,20,9,22
1 1,21,3,4
2 5,16,7
.....

Output:
C1 C2
3 1
3 4
3 10
3 7
4 1
4 2
.....

Reply to this Message

Uri Dimant - 13 Mar 2008 11:41 GMT

Hi
CREATE TABLE #t(c1 int,c2 varchar(50))
INSERT INTO #t VALUES (3,'1,4,10,7')
INSERT INTO #t VALUES (4,'1,2,13,4,15,6,7,11,5,3,20,9,22')

SELECT IDENTITY(INT) "n" INTO Numbers
FROM sysobjects s1
CROSS JOIN sysobjects s2
GO

SELECT C1,SUBSTRING(C2, n, CHARINDEX(',', C2 + ',', n) - n) AS m
from numbers ,#T where substring(','+C2,n,1)=','
AND n < LEN(C2) + 1
ORDER BY C1,cast(SUBSTRING(C2, n, CHARINDEX(',', C2 + ',', n) - n) as int)

> Here is a table where column2 needs to be transposed. The output table
> will be id values in C2 transposed. The values in C2 can have
[quoted text clipped - 16 lines]
> 4 2
> .....

Reply to this Message

Randy Pitkin - 13 Mar 2008 16:48 GMT

Also, if SQL 2005 is the Environment
REview PIVOT

> Hi
> CREATE TABLE #t(c1 int,c2 varchar(50))
[quoted text clipped - 31 lines]
>> 4 2
>> .....

Reply to this Message

bangla2@yahoo.com - 14 Mar 2008 05:30 GMT

> Hi
> CREATE TABLE #t(c1 int,c2 varchar(50))
[quoted text clipped - 37 lines]
>
> - Show quoted text -

Thanks Uri that was great!

Reply to this Message