Converting Rows to Columns

Question

I have a table with columns UserID and CountryName

Now I want get record in this way

[UserId]   [ContryName1] [ContryName2] [ContryName3].........

Use PIVOT I just write an answer very similat to this case. In my case I know the name of the columns before hand, if you dont you may need a dinamic pivot — Juan Carlos Oropeza
– Juan Carlos Oropeza, Commented Sep 29, 2015 at 19:13

Sateesh Pagolu · Accepted Answer · 2015-09-30 05:16:14Z

2

Fiddle here : http://sqlfiddle.com/#!6/cd6f1/1

DECLARE @SQL AS NVARCHAR(MAX)
WITH CTE AS
(
SELECT USERID,COUNTRYNAME,ROW_NUMBER() OVER(PARTITION BY USERID ORDER BY COUNTRYNAME) AS RN
FROM CNTRIES
)

SELECT @SQL = 'WITH CTE1 AS
(
SELECT USERID,COUNTRYNAME,ROW_NUMBER() OVER(PARTITION BY USERID ORDER BY COUNTRYNAME) AS RN
FROM CNTRIES
)
SELECT * 
FROM 
(SELECT USERID,COUNTRYNAME,RN FROM CTE1)C
PIVOT (MAX(COUNTRYNAME) FOR RN IN (['+STUFF((SELECT '],['+CAST(RN AS VARCHAR(100)) 
                                             FROM CTE 
                                             GROUP BY RN  
                                             FOR XML PATH('')),1,3,'')+'])) AS PIVOTT'

answered Sep 30, 2015 at 5:16

Sateesh Pagolu

9,6563 gold badges32 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Steve Mangiameli · Accepted Answer · 2015-09-29 20:58:50Z

PIVOT is your best option if your version is SQL Server 2005 or above, but you don't state the version and trying to use PIVOT without a natural aggregate can be difficult to grasp for some. If your version is below 2005, you have bigger problems. Otherwise, you'll need to left join the table on itself to give you the same result. You can use a ranking function to make it a little easier. Something like this, while inefficient, will produce similar results.

    /*
    IF OBJECT_ID('Countries','U') IS NOT NULL
        DROP TABLE Countries

    CREATE TABLE Countries
    (
          UserID INT
        , CountryName VARCHAR(255)
    )

    INSERT Countries
    VALUES (1, 'India')
        , (1, 'UK')
        , (2, 'USA')
        , (2, 'India') 
        , (2, 'Canada')
    */


    SELECT DISTINCT x.UserID, x.CountryName Country1, y.CountryName Country2, z.CountryName Country3
    FROM Countries c
        LEFT JOIN
        (
            SELECT *, RANK() OVER(PARTITION BY UserID ORDER BY UserID, CountryName) AS UserRank
            FROM Countries
        )x ON x.UserID = c.UserID AND x.UserRank=1
        LEFT JOIN 
        (
            SELECT *, RANK() OVER(PARTITION BY UserID ORDER BY UserID, CountryName) AS UserRank
            FROM Countries
        )y ON y.UserID = c.UserID AND y.UserRank=2
        LEFT JOIN 
        (
            SELECT *, RANK() OVER(PARTITION BY UserID ORDER BY UserID, CountryName) AS UserRank
            FROM Countries
        )z ON z.UserID = c.UserID AND z.UserRank=3

Collectives™ on Stack Overflow

Converting Rows to Columns

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related