CODEWITHSUNDEEP
sqlsql-server
setiasetia
setiasetia

Reputation: 113

SQL Server: column to row

I have a table (table1) with 2 columns toypattern and additionalcolor:

Table 1

toypattern  additionalcolor
FL38        orange
FL38        yellow
LF66        grey
LF66        black

and now I need to write a query to show the toypattern column, 1st color column and 2nd color column. I need to display up to 5 additionalColor options

Desired output:

toypattern  1st color   2nd color     3rd color   4th color   5th color
  FL38        orange      yellow
  LF66        grey        black

How do I write such a SQL query?

Upvotes: 1

Views: 548

Answers (4)

Mike Burton
Mike Burton

Reputation: 3020

SELECT 
   first.toypattern,
   first.additionalColor,
   second.additionalColor
FROM
   Table1 first
   INNER JOIN Table2 second ON first.toypattern = second.toypattern
WHERE
   first.additionalColor < second.additionalColor

Problem being, you'll get back several matches if there are more than two colors per pattern.

edit: This doesn't really get back the results one might wish for when searching for more than 2 colors. However, given the comments attached to this answer I won't delete it.

Upvotes: 0

Jcis
Jcis

Reputation: 173

What Sql Server version do you use? (I can't add comments) A Pivot like the one posted above would be the best, but in case you're using and old Sql Server version (like 2000) you could do this using a CURSOR:

drop table #temp
create table #temp
(
   toypattern varchar(20),
   additionalcolor1 varchar(20),
   additionalcolor2 varchar(20),
   additionalcolor3 varchar(20),
   additionalcolor4 varchar(20),
   additionalcolor5 varchar(20)
)

declare @toypattern as varchar(20)
declare @additionalcolor as varchar(20)     
declare @Counter integer 
declare @StrSql varchar(200)    
declare @LastToyPattern as varchar(20)

set @LastToyPattern = ''

declare MyCursor cursor for  select toypattern, additionalcolor from table1 order by toypattern
open MyCursor
fetch next from MyCursor into @toypattern, @additionalcolor 

   while @@fetch_status = 0     
   begin
      if (@LastToyPattern <> @toypattern)
      begin
         if (@LastToyPattern <> '') 
         begin
            set @StrSql = @StrSql + replicate(',null', 5 - @Counter)    
            exec(@StrSql)
         end
         set @Counter = 0
         set @StrSql = 'insert into #temp select ''' + @toypattern + ''''
      end
      set @StrSql = @StrSql + ', ''' + @additionalcolor + ''''  
      set @Counter = @Counter + 1
      set @LastToyPattern = @toypattern
      fetch next from MyCursor into @toypattern, @additionalcolor
   end
set @StrSql = @StrSql + replicate(',null', 5 - @Counter)
exec(@StrSql)
close MyCursor
deallocate MyCursor
select * from #temp

Upvotes: 0

YS.
YS.

Reputation: 1828

Ok. not sure if this question is related to this question (http://stackoverflow.com/questions/11070509/sql-query-issue-on-left-join):

But here's a solution that might be what you're after - took some of 03Usr's code (the row_number in particular and added pivoting):

;with cte as
(select toypattern, additionalcolor,
  rowno =  row_number() over(partition by toypattern order by additionalcolor)
from Table1)
SELECT
  ToyPattern
  --, ToyName
  , 'color 1' = [1]
  , 'color 2' = [2]
  , 'color 3' = [3]
  , 'color 4' = [4]
  , 'color 5' = [5]
FROM (  
  SELECT
  ToyPattern
  --, ToyName
  , rowno
  , additionalcolor
  FROM
    cte
) AS tac PIVOT (
  MAX (additionalcolor) FOR rowno in ([1],[2],[3],[4],[5])
) AS PivotTable

Upvotes: 2

03Usr
03Usr

Reputation: 3435

Try these:

;with cte as
(select toypattern, additionalcolor,
  row_number() over(partition by toypattern order by additionalcolor) As rn
from Table1)
select toypattern, 
  max(case when rn = 1 then additionalcolor end) as additionalcolor1,
  max(case when rn = 2 then additionalcolor end) as additionalcolor2
from cte
group by toypattern

This returns:

toypattern  additionalcolor1    additionalcolor2
FL38        Orange              Yellow
LF66        Black               Grey

downside of this is you need to add new MAX(...) lines for new additional colours.

////

Another solution would be to get the additional colours as CSV:

SELECT DISTINCT T1.toypattern,
STUFF((SELECT TOP 100 PERCENT ',' + s2.additionalcolor FROM Table1 AS s2 WHERE s2.toypattern = T1.toypattern ORDER BY ',' + s2.additionalcolor FOR XML PATH('')), 1, 1, '') AS Colours
FROM Table1 AS T1
ORDER BY T1.toypattern

This returns:

toypattern  Colours
FL38    Orange,Yellow
LF66    Black,Grey

Hope this helps.

Upvotes: 2

Related Questions