Alter query change columns

• river1

  SSC-Insane

  Points: 24315

  More actions

  February 21, 2014 at 5:15 am

  #288484

  Hi,

  I have a table:

  company_name,

  product_id

  product_name

  Data Example:

  1;1;'oi'

  1;1;'oi2'

  1;2;'teste'

  2;1;'oi3'

  When I make a select I the data ir returned like:

  1;1;'oi'

  1;1;'oi2'

  1;2;'teste'

  2;1;'oi3'

  I want to change the select to show data like:

  1;1;'oi',1,'oi2',2,'teste'

  2;1;'oi3'

  All the data of each company should apper in the same record (line).

  Can someone help?

  Thanks

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  February 21, 2014 at 6:48 am

  #1691075

  This will work with your "sample" data.

  -- sample data

  DROP TABLE #Whatnot

  CREATE TABLE #Whatnot (company_name int, product_id int, product_name varchar(10))

  INSERT INTO #Whatnot VALUES

  (1,1,'oi'),

  (1,1,'oi2'),

  (1,2,'teste'),

  (2,1,'oi3')

  -- solution

  SELECT

  company_name,

  col1 = MAX(CASE WHEN rn = 1 THEN product_id END),

  col2 = MAX(CASE WHEN rn = 1 THEN product_name END),

  col3 = MAX(CASE WHEN rn = 2 THEN product_id END),

  col4 = MAX(CASE WHEN rn = 2 THEN product_name END),

  col5 = MAX(CASE WHEN rn = 3 THEN product_id END),

  col6 = MAX(CASE WHEN rn = 3 THEN product_name END)

  FROM (

  SELECT *, rn = ROW_NUMBER() OVER(PARTITION BY company_name ORDER BY (SELECT NULL)) FROM #Whatnot

  ) d

  GROUP BY company_name

  ORDER BY company_name

  If you have an unknown number of output columns, you will have to resort to dynamic SQL as explained in this excellent article by Jeff Moden[/url].

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden