question

chetanserantec avatar image
chetanserantec asked

varchar value pivot

DECLARE @tbl as table ( AutoID INT IDENTITY ,GroupName VARCHAR(10) ,TeamName VARCHAR(50) ) INSERT INTO @tbl ( GroupName ,TeamName ) VALUES('GroupA','Team1' ) ,('GroupA','Team2' ) ,('GroupA','Team3' ) ,('GroupB','Team4' ) ,('GroupB','Team5' ) ,('GroupB','Team6' )

SELECT * FROM @tbl

please help me to get this result

GroupA GroupB

Team1 Team4

Team2 Team5

Team3 Team7

pivot
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
ThomasRushton avatar image
ThomasRushton answered

Couple of options:

1. The CrossTab - might be more efficient. Difficult to say. Easier to read / understand / maintain than the pivot, particularly when you get into dynamic sql...

2. The Pivot.

See @Jeff Moden's article "Cross Tabs And Pivots, Part One" for discussion on both these methods.

Either way, you need something to organise your data so that you know which lines are going where. Here, I've used a ROW_NUMBER() field partitioned by GroupName and ordered by TeamName.

SELECT AutoID,
       GroupName,
       TeamName,
       ROW_NUMBER() OVER (PARTITION BY GroupName ORDER BY TeamName) AS rn
FROM @tbl; 

The query above shows what the ROW_NUMBER field is. What we then do is use that to pair the rows up.

Option 1: Crosstab

WITH
r
    AS
    (
        SELECT AutoID,
               GroupName,
               TeamName,
               ROW_NUMBER() OVER (PARTITION BY GroupName ORDER BY TeamName) AS rn
        FROM @tbl
    )
SELECT MAX(CASE WHEN r.GroupName = 'GroupA' THEN TeamName END) AS GroupA,
       MAX(CASE WHEN r.GroupName = 'GroupB' THEN TeamName END) AS GroupB
FROM r
GROUP BY rn; 

Option 2: Pivot

SELECT --rn,
       MAX([GroupA]) AS GroupA,
       MAX([GroupB]) AS GroupB
FROM
(
    SELECT AutoID,
           GroupName,
           TeamName,
           ROW_NUMBER() OVER (PARTITION BY GroupName ORDER BY TeamName) AS rn
    FROM @tbl
) AS r
PIVOT (MAX(TeamName)
FOR GroupName IN ([GroupA], [GroupB])
      ) AS p
GROUP BY rn;

As for performance? Checking the execution plans based on these tiny datasets isn't helpful...

Have fun.

1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

thank you so much for your help ThomasRushton

0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.