Pivot or Transpose a row

I would like to transpose/pivot a table that exists:

2 1 2 2 5
3 3 3 1 5
2 2 2 1 4
4 4 4 5 1
2 2 2 1 4
2 2 1 5 5

to:

1 1 2

1 2 3

1 3 2

1 4 4

1 5 2

1 6 2

2 1 1

2 2 3

2 3 2

2 4 4

2 5 2

2 6 2

Just looking at the first 6 rows of the new table:

Basically every column is copied to the third column of the new table.( 1,1) becomes (1,3)....(2,1) becomes (2,3)

There are 6 rows in the original table so the second column in the new table is a reoccuring count from 1 to 6. There is a value of 1 in the first column of the new table(first 6 rows).

The logic in the old table is: every column contains 6 answers from a particular user.

In the new table (1,1) represents the user, (1,2) represents the question, (1,3) represents the users answer for the question.

Is there a quick way to do this in Transact sql using a cursor and the and pivot function?

Any help would be appreciated!

There is not such concept as row number in SQL Server, so we will need a criteria to sort the table. Let us suppose that the table has a column with identity property, then we can use:

Code Snippet

create table dbo.t1 (

row_id int not null identity primary key,

c1 int,

c2 int,

c3 int,

c4 int,

c5 int

)

go

insert into dbo.t1(c1, c2, c3, c4, c5) values(2, 1, 2, 2, 5)

insert into dbo.t1(c1, c2, c3, c4, c5) values(3, 3, 3, 1, 5)

insert into dbo.t1(c1, c2, c3, c4, c5) values(2, 2, 2, 1, 4)

insert into dbo.t1(c1, c2, c3, c4, c5) values(4, 4, 4, 5, 1)

insert into dbo.t1(c1, c2, c3, c4, c5) values(2, 2, 2, 1, 4)

insert into dbo.t1(c1, c2, c3, c4, c5) values(2, 2, 1, 5, 5)

go

select

b.c1 as c_id,

a.row_id,

case b.c1

when 1 then a.c1

when 2 then a.c2

when 3 then a.c3

when 4 then a.c4

when 5 then a.c5

end as c3

from

dbo.t1 as a

cross join

(

select 1 as c1

union all

select 2 as c1

union all

select 3 as c1

union all

select 4 as c1

union all

select 5 as c1

) as b

order by

c_id,

a.row_id

go

-- SS 2005

select

c_id,

row_id,

val

from

(

select

row_id,

c1 as [1],

c2 as [2],

c3 as [3],

c4 as [4],

c5 as [5]

from

dbo.t1

) as pvt

unpivot

(val for c_id in ([1], [2], [3], [4], [5])) as unpvt

order by

c_id,

row_id

go

drop table dbo.t1

go

AMB

|||

Here is one way:

select n,
row_number() over ( partition by n order by current_timestamp ),
case n when 1 then c1
when 2 then c2
when 3 then c3
when 4 then c4
when 5 then c5
end
from tbl cross join
( select 1 union
select 2 union
select 3 union
select 4 union
select 5 ) D ( n )

Here tbl is your table and c1, c2, ... denotes the columns. The row_number() function is used to number the tables and the order by current_timestamp is simply a shortcut to generate the sequence without any reliance on existing columns.

--

Anith

|||

Hello

This works too:

SELECT IDENTITY(int, 1,1) AS ID_Num, t.*
INTO #TestResults
FROM TestResults t

CREATE TABLE dbo.NewResults (iUser INT, iQuestion INT, iAnswer INT)

DECLARE @.iColumns INT, @.lcSQL VARCHAR(200)
SET @.iColumns = 1

WHILE @.iColumns <= 5 -- Number of result columns in table TestResults
BEGIN
SET @.lcSQL = 'INSERT INTO dbo.NewResults (iUser, iQuestion, iAnswer) SELECT 1, ID_Num, c' + CAST(@.iColumns AS VARCHAR(2)) + ' FROM #TestResults'
EXEC(@.lcSQL)
SET @.iColumns = @.iColumns + 1
END

DROP TABLE #TestResults

SELECT * FROM dbo.NewResults

|||

Anith could you please explain the sql syntax?

over ( partition by n order by current_timestamp

and

D ( n )

thanks!