SQL Pivot table with string fields

Question

I am new with SQL Pivot, so i would like to have a detail explaination regarding to it. My table is like the following:

PID   | NAME |  PlateNumber  |  COUNTRY|
------------------------------------------
111   | Alex |  ab123456     |  GB      |
------------------------------------------
111   | Alex |  fe123344     |  ES      |
------------------------------------------
111   | Alex |  r223456e     |  US      |
------------------------------------------
112   | Simon |  t22er563    |  GB      |
------------------------------------------
112   | Simon |  q32345ke    |  DE      |
------------------------------------------
113   | Ben   |  ve923456    |  IT      |
------------------------------------------

And i would to have the result in the following format:

PID |NAME |PlateNumber1|PlateNumber2| PlateNumber3|COUNTRY1| COUNTRY2| COUNTRY3|
--------------------------------------------------------------------------------
111 | Alex |  ab123456 |  fe123344  | r223456e    | GB     | ES      | US     |
--------------------------------------------------------------------------------
112 | Simon |  t22er563|  q32345ke  |             | GB     | DE      |        |
--------------------------------------------------------------------------------
113 | Ben   |  ve923456|            |             | IT     |         |        |
--------------------------------------------------------------------------------

Could you please help me with this?

Thank you in advance!

thank you guys for your answer, i havent got chance to try it out. but i will see how it goes — Calabaza
– Calabaza, Commented Mar 9, 2017 at 11:09

SqlZim · Accepted Answer · 2017-03-01 18:02:48Z

1

The traditional cross tab / conditional aggregation version would be like so:

test setup: http://rextester.com/SKMUL25726

select 
    pid
  , name
  , PlateNumber1 = max(case when rn = 1 then PlateNumber end)
  , PlateNumber2 = max(case when rn = 2 then PlateNumber end)
  , PlateNumber3 = max(case when rn = 3 then PlateNumber end)
  , Country1 = max(case when rn = 1 then Country end)
  , Country2 = max(case when rn = 2 then Country end)
  , Country3 = max(case when rn = 3 then Country end)
from (
  select t.*
    , rn=row_number() over (partition by PID order by platenumber) 
  from t
  ) as t
group by pid, name

returns:

+-----+-------+--------------+--------------+--------------+----------+----------+----------+
| pid | name  | PlateNumber1 | PlateNumber2 | PlateNumber3 | Country1 | Country2 | Country3 |
+-----+-------+--------------+--------------+--------------+----------+----------+----------+
| 111 | Alex  | ab123456     | fe123344     | r223456e     | GB       | ES       | US       |
| 113 | Ben   | ve923456     | NULL         | NULL         | IT       | NULL     | NULL     |
| 112 | Simon | q32345ke     | t22er563     | NULL         | DE       | GB       | NULL     |
+-----+-------+--------------+--------------+--------------+----------+----------+----------+

answered Mar 1, 2017 at 18:02

SqlZim

38.2k6 gold badges45 silver badges61 bronze badges

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

7 Comments

Gurwinder Singh Over a year ago

Didn't even realize that. +1

SqlZim Over a year ago

@GurV you should restore your answer though, it was the only way to do it with pivot().

Calabaza Over a year ago

@SqlZim, Can i ask you how to insert this into a table? i have tried put select * into table a from (this query) but it does not work :(

SqlZim Over a year ago

@Zilin If table_a exists, then use insert into table_a (...) select... If table_a doesn't exist, use select ... into table_a from ...

Calabaza Over a year ago

@SqlZim yeah, table a doesnt exist, thats why im using select * into table a from (the query), but it doesnt work, do you know what could possible cause this?

|

Gurwinder Singh · Accepted Answer · 2017-03-01 18:12:22Z

1

Using PIVOT:

with your_table(PID   , NAME  ,  PlateNumber ,  Country) as (
    select 111   , 'Alex'  ,  'ab123456'   ,  'GB' union all
    select 111   , 'Alex'  ,  'fe123344'   ,  'ES' union all
    select 111   , 'Alex'  ,  'r223456e'   ,  'US' union all
    select 112   , 'Simon' ,  't22er563'   ,  'GB' union all
    select 112   , 'Simon' ,  'q32345ke'   ,  'DE' union all
    select 113   , 'Ben'   ,  've923456'   ,  'IT'
)
select pid,
    name,
    max(PlateNumber1) PlateNumber1,
    max(PlateNumber2) PlateNumber2,
    max(PlateNumber3) PlateNumber3,
    max(Country1) Country1,
    max(Country2) Country2,
    max(Country3) Country3
from (
    select *,
        'Country' + cast(row_number() over (
                partition by PID order by pnum
                ) as varchar(30)) cn
    from (
        select t.*,PlateNumber pnum,
            'PlateNumber' + cast(row_number() over (
                    partition by PID order by PlateNumber
                    ) as varchar(30)) pn
        from your_table t
        ) t
    pivot(max(PlateNumber) for pn in ([PlateNumber1], [PlateNumber2], [PlateNumber3])) x
    ) t
pivot(max(Country) for cn in ([Country1], [Country2], [Country3])) x
group by pid,
    name;

Demo

edited Mar 1, 2017 at 18:12

answered Mar 1, 2017 at 18:02

Gurwinder Singh

39.7k6 gold badges62 silver badges87 bronze badges

1 Comment

SqlZim Over a year ago

this might mix up the platenumber with the country in which it was issued (if that is even a thing with this question). keeping the same order by will prevent that.

Nolan Shang · Accepted Answer · 2017-03-01 20:19:18Z

If the PlateNumber count is not fixed of PID, you can use dynamic statement.

    CREATE  TABLE #tt(PID INT,[Name] VARCHAR(10),PlateNumber VARCHAR(10),Country VARCHAR(5))
    INSERT INTO #tt
    select 111   , 'Alex'  ,  'ab123456'   ,  'GB' union all
    select 111   , 'Alex'  ,  'fe123344'   ,  'ES' union all
    select 111   , 'Alex'  ,  'r223456e'   ,  'US' union all
    select 112   , 'Simon' ,  't22er563'   ,  'GB' union all
    select 112   , 'Simon' ,  'q32345ke'   ,  'DE' union all
    select 113   , 'Ben'   ,  've923456'   ,  'IT'

    DECLARE @SQL VARCHAR(max),@col VARCHAR(max)
    ----   Get the max line count of all the PID
    DECLARE @MaxNumber INT =0
    SELECT @MaxNumber=CASE WHEN COUNT(0)>@MaxNumber THEN count(0) ELSE @MaxNumber END  FROM #tt AS t GROUP BY t.Name,t.PID
    PRINT @MaxNumber
    SELECT @col=ISNULL(@col+',','')+'PlateNumber'+LTRIM(sv.number)+',Country'+LTRIM(sv.number)
    FROM  master.dbo.spt_values AS sv WHERE sv.type='P' AND sv.number BETWEEN 1 AND @MaxNumber
    PRINT @col
    SET @SQL='
    SELECT * FROM (
        SELECT pid,[name],c.col_value,c.col_title+LTRIM(ROW_NUMBER()OVER(PARTITION BY t.PID,[name],c.col_title ORDER BY (SELECT 0))) AS col_title FROM #tt AS t
         CROSS APPLY(VALUES(''PlateNumber'',t.PlateNumber),(''Country'',t.Country))c(col_title,col_value)
    ) AS t
    PIVOT(MAX(col_value) FOR col_title IN ('+@col+')) p'
    EXEC(@SQL)

pid         name       PlateNumber1 Country1   PlateNumber2 Country2   PlateNumber3 Country3
----------- ---------- ------------ ---------- ------------ ---------- ------------ ----------
111         Alex       r223456e     GB         fe123344     ES         ab123456     US
113         Ben        ve923456     IT         NULL         NULL       NULL         NULL
112         Simon      q32345ke     DE         t22er563     GB         NULL         NULL

Collectives™ on Stack Overflow

SQL Pivot table with string fields

3 Answers 3

7 Comments

Demo

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

7 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related