Transpose Rows in SQL Server

Question

0.00/5 (No votes)

See more:

SQL-server-2000

SQL-Server-2005

, +

I have a table Table1 as follows

ID	Action 	Value
1	BEGIN	1
1	END	5
2	BEGIN	20
2	END	25
1	BEGIN	10
1	END	15

Please help me to write a TSQL query which create a result set as follows.

ID	BEGIN	END
1	1	5
2	20	25
1	10	15

Posted 22-Jan-12 23:27pm

Liju Sankar

Updated 22-Jan-12 23:32pm

v2

Add a Solution

Comments

[no name] 23-Jan-12 8:38am

Thank you all for your support, i am submitting two approach to resolve my scenario

5 solutions

Solution 3

Use PIVOT - UNPIVOT

Using PIVOT and UNPIVOT[^]

Pivoting data in SQL Server[^](Alternate ways)

Posted 23-Jan-12 0:13am

thatraja

Comments

[no name] 23-Jan-12 6:31am

Thanks Tharaja,

I am facing challenges in using PIVOT, please see the 'Solution2', the aggregate used in PIVOT is not retrieving expected data.

[no name] 23-Jan-12 6:34am

my expected Result
ID BEGIN END
1 1 5
2 20 25
1 10 15

Pivot query is retrieving (because Aggregate SUM is used in PIVOT)
ID BEGIN END
1 11 20
2 20 25

Amir Mahfoozi 26-Jan-12 5:20am

+5

Solution 1

This should be accomplish using PIVOT

read this

http://msdn.microsoft.com/en-us/library/ms177410.aspx[^]

Posted 22-Jan-12 23:53pm

RDBurmon

Comments

[no name] 23-Jan-12 6:31am

Thanks Rahul,
I am facing challenges in using PIVOT, please see the 'Solution2', the aggregate used in PIVOT is not retrieving expected data.

Solution 2

hi try with this,

SQL

SELECT id, [BEGIN] , [END]
FROM
(SELECT id, action, value
FROM temp) ps
PIVOT
(
SUM (value)
FOR [action] IN
( [BEGIN], [END])
) AS pvt
group by  id,[BEGIN],[END]

Posted 23-Jan-12 0:06am

palraj001

Comments

[no name] 23-Jan-12 6:29am

Thanks,

This query will not work retrieve the correct result, its taking sum of all values in ID '1' and ID '2'.

[no name] 23-Jan-12 6:32am

my expected Result
ID BEGIN END
1 1 5
2 20 25
1 10 15

Pivot query is retrieving (because Aggregate SUM is used in PIVOT)
ID BEGIN END
1 11 20
2 20 25

palraj001 23-Jan-12 6:56am

if you want to make this out put , you have to add another column for grouping ID

palraj001 23-Jan-12 7:08am

1 BEGIN 1 1
1 BEGIN 10 2
1 END 5 1
1 END 15 2
2 BEGIN 20 3
2 END 25 3

[no name] 23-Jan-12 8:42am

Another solution is submitted in 'Solution5', please review

Solution 4

I think it will never work, since line 1 and 2 and line 5 and 6 have the same ID. There is no way of knowing for the SQL Server, which lines belong together.

Assuming you have ID 3 in line 5 and 6 you can use this query:

ID	Action 	Value
1	BEGIN	1
1	END	5
2	BEGIN	20
2	END	25
3	BEGIN	10
3	END	15

SQL

select
    b.id, b.value as [BEGIN], e.value as [END]
from
    t1 b
    inner join t1 e on b.id = e.id
where
    b.action = 'BEGIN'
    and e.action = 'END'

Result:

id	BEGIN	END
1	1	5
2	20	25
3	10	15

Posted 23-Jan-12 0:52am

Heino Zunzer

Updated 23-Jan-12 1:02am

v3

Comments

[no name] 23-Jan-12 8:39am

thank you,
Please find Solution 5 for a few approaches to handle this.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Liju Sankar · Accepted Answer · 2012-01-23T02:34:00

Solution: 1

SQL

select * from (
select ID,[Action],value from @TableData) s_tab pivot (
max(value)  for [Action] in ([BEGIN],[END])) as pvt

union

select * from (
select ID,[Action],value from @TableData) s_tab pivot (
min(value)for [Action] in ([BEGIN], [END])) as pvt

Solution 2

SQL

--Select Query
SELECT Beginning.ID AS [ID]
         ,Beginning.Value AS [Begin]
         ,Ending.Value AS [End]
FROM @Table1 AS Beginning
      OUTER APPLY (
                        SELECT TOP 1 Value
                        FROM @Table1 AS ext
                        WHERE ext.[Action] = 'END'
                                AND ext.ID = Beginning.ID
                                AND ext.Value > Beginning.Value
                        ORDER BY ext.Value ASC
                   ) AS Ending
WHERE [Action] = 'BEGIN'