Transpose Microsoft SQL Server table

Question

I have two tables, a header table and a detail table. The data looks like this:

    OBJ_NO | Name
    12345  | Fred
    67891  | Bob

Detail table:

    OBJ_NO | HEADER_OBJ_NO | CODE
    1      | 12345         | Red
    2      | 12345         | Blue
    3      | 12345         | Green
    4      | 67891         | Red
    5      | 67781         | Green

Essentially what I am after is to see something like this:

    OBJ_NO | Name | Red | Blue | Green
    12345  | Fred | 1   | 1    | 1
    67891  | Bob  | 1   | 0    | 1

It could be different number of "Colours" as well. Its not set. And not each Header Ref would have one of each colour as shown in the example above.

How would I achieve this?

This question has been asked before but here is a sql fiddle with some helpful code for your problem -- http://sqlfiddle.com/#!3/8c731/5 — Taryn, Apr 16 '14 at 14:38
Is this a duplicate question? The data do not contain 1s and 0s which is the desired result. And only one column is pivoted. Not all rows and all columns. — smoore4, Apr 16 '14 at 15:55
You could use any number of these as a dup including [this](http://stackoverflow.com/questions/14979220/counting-values-in-a-column-separately) and [this](http://stackoverflow.com/questions/12643117/dynamically-create-columns-sql/12643408#12643408) — Taryn, Apr 16 '14 at 16:24
Yes of course. I actually think that almost half of any MSSQL or MySQL questions are dupes. I usually ignore most, but this question was interesting and seemed like a real use-case, which is why I answered it. Your sqlfiddle was very good btw. — smoore4, Apr 16 '14 at 16:34

smoore4 · Answer 1 · 2014-04-16T15:00:35.813

-1

A SQL Server PIVOT will work for you.

  WITH CTE(OBJ_NO, NAME, RED, BLUE, GREEN)
  AS
  (
  SELECT [OBJ_NO]
      ,[NAME]
      ,[red],[blue],[green]
  FROM (SELECT T2.[OBJ_NO]
      ,T2.[NAME], T1.CODE FROM [dbo].[Table_2] T2
   INNER JOIN [dbo].[Table_1] T1 ON T1.HEADER_OBJ_NO = T2.OBJ_NO ) AS SourceTable
   PIVOT
   (MAX(CODE) 
   FOR CODE IN ([red], [blue], [green])
   )AS PivotTable
   )
   SELECT OBJ_NO
   ,NAME
   ,RED = CASE WHEN RED = 'red' THEN 1 ELSE 0 END
   ,BLUE = CASE WHEN BLUE = 'blue' THEN 1 ELSE 0 END
   ,GREEN = CASE WHEN GREEN = 'green' THEN 1 ELSE 0 END
   FROM CTE

edited Apr 16 '14 at 15:00

answered Apr 16 '14 at 14:48

smoore4

4,520
3
36
55

What's with the down vote? IMHO, this is not a dupe question. The other is about transposing all rows and all columns. This is similar but different. And the pivot solution in the "dupe" first UNPIVOTs then PIVOTs. Mine does not do that. It is also the simplest of responses. While STUFF with FOR XML works, I am not sure if all DBAs know the syntax off the top of their heads. I am not saying it is best, but it shouldn't be discounted because it is not complex. Quite the contrary. – smoore4 Apr 16 '14 at 15:48

Transpose Microsoft SQL Server table

1 Answers1