SQL Combine Multiple rows into Multiple Columns

Question

I have been looking at the other example here and across the web and I just cannot wrap my head around how to do this.

I have a site where I am pulling every piece of information on products into the one table to be viewed and then loaded into a csv/excel sheet.

It combines 7 tables and spits them out as below: (cut off the rest of the table to make easily readable)

ID  Filter     FilterValue  ParentItem  ItemTitle             
---------------------------------------------------------------
7   15         B            LE0001      LB PALETTE WHITE 127MM
7   16         Yes          LE0001      LB PALETTE WHITE 127MM  
7   18         Yes          LE0001      LB PALETTE WHITE 127MM
7   20         Std          LE0001      LB PALETTE WHITE 127MM
7   22         Yes          LE0001      LB PALETTE WHITE 127MM
7   23         Polyester    LE0001      LB PALETTE WHITE 127MM
7   25         White        LE0001      LB PALETTE WHITE 127MM
7   26         127mm        LE0001      LB PALETTE WHITE 127MM

Using this code (The current columns showing are tblprod.prodID, prodval.valueColumn, prodval.valueKey, tblprod.parentSKU, and tblprod.prodTitle )

SELECT 
   tblprod.prodID as ID, prodval.valueColumn as Filter, 
   prodval.valueKey as FilterValue, tblprod.prodSKU as Item, 
   tblprod.parentSKU as ParentItem, tblprod.prodTitle as ItemTitle, 
   tblprod.prodConsumerTitle as ConsumerTitle, tblprod.itemGroup as ItemGroup, 
   tblprod.itemFamily as ItemFamily, tblprod.cutLengthCode as LengthCode, 
   tblprod.prodPackSize as PackSize, tblprod.prodMultQty as MultipleQuantity, 
   tblsu.ImportCode as SalesUnit, tblprod.prodPrice as SalesPrice , 
   tblprod.qtyDependent as QtyDep, tblprod.qtyTarget as Limit, 
   tblprod.targetPrice as Price, tblprod.prodLegacyImage as MainImage,
   catprod.IsPrimary as SafetyinMind, featlists.prodList as NEWLimited      
FROM 
   [Eclipse].[dbo].[tbl_Product] AS tblprod
LEFT JOIN 
   [Database].[dbo].[tbl_SalesUnit] AS tblsu ON tblsu.ID = tblprod.saleUnit
LEFT JOIN 
   [Database].[dbo].[tbl_ProductFilter] AS prodfil ON prodfil.ProdID = tblprod.prodID
LEFT JOIN 
   [Database].[dbo].[tbl_ProductFilterValues] AS prodval ON prodval.valueID = prodfil.valueID
LEFT JOIN
   [Database].[dbo].[tbl_ProductstoFeaturedLists] AS prodlists ON prodlists.prodID = tblprod.prodID
LEFT JOIN
   [Database].[dbo].[tbl_FeaturedLists] AS featlists ON featlists.ID = prodlists.listID
LEFT JOIN 
   [Database].[dbo].[tbl_CategoryProducts] AS catprod ON catprod.prodID = tblprod.prodID

The important part is I want to combine the rows of the same ID, create column titles out of the Filter column (prodval.valueColumn) and fill them with their corresponding value (prodval.valueKey)

My issue is I just have no idea how to accomplish this and I am lost when reading the other answers, secondly there are 19 filters and not every product will have all of them (as you can see the above product has 8) I am unsure if this will cause me any issues when I do this. The filters range from 15 to 33, all of them are used but just by different products.

An example table would look like below.

ID  ParentItem  ItemTitle               Filter 15   Filter 16   Filter 17   Filter 18   Filter 19   Filter 20   Filter...
7   LE0001      LB PALETTE WHITE 127MM  B           YES                     YES                     Std

If anyone could offer any help I would seriously appreciate it, I just cannot get my head around it.

Sorry forgot to mention I am using SQL Server Management Studio

what is the select max(Filter),min(Filter) from yourtable? — Deep
– Deep, Commented Dec 12, 2014 at 12:28

Deep · Accepted Answer · 2014-12-12 13:31:14Z

1

try below solution :

DECLARE @Columns VARCHAR(max)
;WITH cte
     AS (SELECT Min(Filter) minimum,
                Max(Filter) maximum
         FROM   yourtable)
SELECT @Columns = Stuff((SELECT ',' + '[ ' + CONVERT(VARCHAR(30), number, 121) + ']'
                         FROM   master..spt_values N
                         WHERE  EXISTS (SELECT 1
                                        FROM   cte
                                        WHERE  n.number BETWEEN cte.minimum AND cte.maximum)
                                AND type = 'P'
                         FOR XML PATH('')), 1, 1, '')

DECLARE @sql NVARCHAR(max)= 'select [ID],[ParentItem],[ItemTitle],' + @Columns + 
                            ' from (select [ID],[ParentItem],[ItemTitle],[Filter],[FilterValue] from yourtable) t 
                            pivot
                            (MAx(FilterValue)
                            FOR Filter in(' + @Columns + ')
                            )as pvt'

EXEC sp_executesql
  @sql

sqlfiddle

edited Dec 12, 2014 at 13:31

answered Dec 12, 2014 at 13:25

Deep

3,2021 gold badge15 silver badges21 bronze badges

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

3 Comments

Francis Over a year ago

That worked thank you. I dont quite understand why but I just need to spend time going over it. Much appreciated.

Deep Over a year ago

It is called dynamic pivot. First, I created list of additional columns that you need and stored it in @columns then I selected fixed columns and dynamic columns from subquery query select [ID],[ParentItem],[ItemTitle],[Filter],[FilterValue] from yourtable. The values of dynamic columns are filled by pivot block.

Francis Over a year ago

Ahh that makes a lot of sense. Appreciated thank you.

Saravana Kumar · Accepted Answer · 2014-12-12 14:07:52Z

Use PIVOT to get the result. Fiddler Demo

CREATE TABLE #Sample
(
    ID INT,  
    Filter INT,    
    FilterValue VARCHAR(100),  
    ParentItem VARCHAR(100),  
    ItemTitle VARCHAR(100)
)

INSERT INTO #Sample VALUES(7,   15,'B',   'LE0001','LB PALETTE WHITE 127MM')
INSERT INTO #Sample VALUES(7,   16,'Yes', 'LE0001','LB PALETTE WHITE 127MM')  
INSERT INTO #Sample VALUES(7,   18,'Yes', 'LE0001','LB PALETTE WHITE 127MM')
INSERT INTO #Sample VALUES(7,   20,'Std', 'LE0001','LB PALETTE WHITE 127MM')
INSERT INTO #Sample VALUES(7,   22,'Yes', 'LE0001','LB PALETTE WHITE 127MM')
INSERT INTO #Sample VALUES(7,   23,'Polyester',    'LE0001','LB PALETTE WHITE 127MM')
INSERT INTO #Sample VALUES(7,   25,'White','LE0001','LB PALETTE WHITE 127MM')
INSERT INTO #Sample VALUES(7,   26,'127mm','LE0001','LB PALETTE WHITE 127MM')


SELECT P.ID,P.ParentItem, P.ItemTitle, 
 Min(Filter15) Filter15,
 Min(Filter16) Filter16 ,
 Min(Filter17)Filter17,
 Min(Filter18)Filter18,
 Min(Filter19)Filter19,
 Min(Filter20)Filter20,
 Min(Filter21)Filter21,
 Min(Filter22)Filter22,
Min(Filter23)Filter23,
Min(Filter24)Filter24,
Min(Filter25)Filter25,
Min(Filter26)Filter26,
Min(Filter27)Filter27,
Min(Filter28)Filter28,
Min(Filter29)Filter29,
Min(Filter30)Filter30,
Min(Filter31)Filter31,
Min(Filter32)Filter32,
Min(Filter33)Filter33
FROM 
 (SELECT *,
               'Filter' + CONVERT(VARCHAR(30), Filter)  AS Filter_A

        FROM   #Sample
 ) AS A

 PIVOT (Min(A.FilterValue) FOR Filter_A in 
      (Filter15,Filter16,Filter17,Filter18,Filter19,
       Filter20,Filter21,Filter22,Filter23,Filter24,
       Filter25,Filter26,Filter27,Filter28,Filter29,
       Filter30,Filter31,Filter32,Filter33)) AS P
GROUP BY P.ID,P.ParentItem, P.ItemTitle

Collectives™ on Stack Overflow

SQL Combine Multiple rows into Multiple Columns

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related