0

I have a table with columns UserID and CountryName

Now I want get record in this way 

[UserId]   [ContryName1] [ContryName2] [ContryName3].........
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Ahmad Jamal
  • 216
  • 2
  • 10
  • 1
    Use `PIVOT` I just write an [**answer**](http://stackoverflow.com/questions/32849539/sql-server-populate-a-table-based-on-another-table-with-a-substring-as-column-na/32849645#32849645) 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**](http://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query) – Juan Carlos Oropeza Sep 29 '15 at 19:13

2 Answers2

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'
Sateesh Pagolu
  • 9,282
  • 2
  • 30
  • 48
0

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
Steve Mangiameli
  • 688
  • 7
  • 15