CODEWITHSUNDEEP
t-sqlsql-server-2005
deutschZuid
deutschZuid

Reputation: 1058

How to reverse-pivot a row into two rows?

Suppose I have the following table:

Row_ID String1 String2    Int1 Int2
1      'James' 'Jiao'     1    2
2      'Jack'  'Ripper'   1    3

I want it to look like this:

Row_ID String     Int
1      'James'    1
1      'Jiao'     2
2      'Jack'     1
2      'Ripper'   3

Note that the Int1/Int2 are never the same. They are unique from each other.

Not sure how to go about this really. Got everything else ready to go, but got stuck on this. I suspect some sort of reverse-pivotting would work? The String column is not finite set, neither is the Int column, so unpivot doesn't seem to work here.

I am trying to avoid using a cursor to go through each row, selecting it into two rows, insert them into a temp table and so on and so forth. This approach will surely work, but is there a better, set-based approach?

Upvotes: 2

Views: 2022

Answers (3)

Pero P.
Pero P.

Reputation: 26992

This has the advantage over UNION of only doing a single scan of the input data.

--2008+
SELECT ROW_ID, String, [Int]
FROM YourTable
    CROSS APPLY(VALUES (String1, Int1), (String2, Int2)) AS A (String, [Int]);

Or as recommended by @MartinSmith since the VALUES syntax is 2008+ (still a single scan)

--2005+ compatible
SELECT ROW_ID, String, [Int]
FROM YourTable
    CROSS APPLY(SELECT String1, Int1 
                UNION ALL 
                SELECT String2, Int2) AS A (String, [Int]);

Upvotes: 3

SqlACID
SqlACID

Reputation: 4014

Try this:

SELECT row_id, string1, int1 from myTable
UNION
SELECT row_id, string2, int2 from myTable

Upvotes: 1

cdaddr
cdaddr

Reputation: 1340

Make two temporary tables or selects --- one selects RowID, String1 and Int1, and the other selects RowID, String2 and Int2. Then alias or label the String columns to just String, and the Int columns to just Int. Then UNION ALL the two of those together to get your result.

But I'm not familiar with T-SQL, this is just an answer from the realm of SQL in general.

I did notice that you don't want to use temporary tables, but if you just make two selects UNIONed together, doesn't your db work out a much faster way to get the job done that doesn't actually involve a row-by-row cursor?

Upvotes: 2

Related Questions