0

I have an output containing fields ID, PRODUCT, BARCODE, TYPEDESC and VALUEDESC. Each product can have multiple TypeDesc with a ValueDesc. Example

enter image description here

I need to PIVOT this, but with the TYPEDESC not being unique, that will create a new column for each TypeDesc.

The desired output would look like this

enter image description here

Where VALUEx and TYPEx would go as far as what there are TypeDesc for each ID. I'm not sure how to go about it, I don't think I'm approaching this the right way

SELECT * FROM ( SELECT ID,PRODUCT,BARCODE,TYPEDESC,VALUEDESC FROM TABLE1 ) SOURCE

PIVOT (
        VALUEDESC  FOR 
        TYPEDESC IN (SELECT TYPEDESC FROM SOURCE)
    ) AS NEWTABLE

Is this going to require a for loop or cursor? Any assistance would be appreciated.

UPDATE: PIVOT is not going to work here. Im going to have to loop through each Product, and populate a table that has an amount of Columns (TypeDesc1, ValueDesc1, 2,3,4,5...) to match select max(cnt) from ( select product,Typedesc,count(1) as cnt from Table1 group by product,typedesc) x

If the loop stops at 3, columns 4-end would be blank. Could this work ?

Ivan Nel
  • 319
  • 3
  • 12
  • 2
    You can't construct a single query that produces an unknown number of columns - any given query always produces a result set with a fixed shape - the number of columns, their names and types. You can either go very ugly with dynamic SQL or pick a tool designed around *presentation* such as a reporting tool or application to process this data, rather than trying to do it in the database. – Damien_The_Unbeliever Feb 11 '21 at 09:17
  • Please don't SHOUT at us in the title. Writing all in caps can come across as rude, as can mean users are far less inclined to read your question, or perhaps give it downvotes. Use your inside voice (the one that uses proper capitilisation where needed, like at the start of a sentence). – Thom A Feb 11 '21 at 09:17
  • Please don't use images for code, data or errors. For sample data use DDL+DML, and for the rest use formatted text. – Dale K Feb 11 '21 at 09:24
  • Calculate a sequence number for each product and that calculated value can become column header https://stackoverflow.com/questions/64400911/pivot-query-in-ms-access. However, you want to pivot on 2 sets of values which complicates http://allenbrowne.com/ser-67.html#MultipleValues. Both references are for Access but something comparable should be possible in SQLServer. – June7 Feb 11 '21 at 09:45

0 Answers0