Help required creating an SQL PIVOT

Question

Help required creating an SQL PIVOT

Shabbaranks

SSC Eights!

Points: 880
More actions
January 3, 2020 at 12:50 pm

#3712089

Hi,
Im struggling to PIVOT SQL data in a single table which links to additional tables via the column\key TraceableItemID.
In the attached example you can see TraceableItemID has the same value for the different attributes but the table stores the attributes on different rows. What I would like to do is have all the corresponding TraceableItemID's attributes across the columns as per example 2.
Can anyone help please?
Attachments:
You must be logged in to view attached files.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Thom A SSC Guru Points: 99130 More actions · Answer 1

Images of the data really don't help us, I'm afraid. We can't consume the information and you shouldn't expect us to transcribe it.

Personally, if you do need to Pivot your data, I recommend using a Cross Tab, rather than the PIVOT operator. PIVOT is quite restrictive, and many actually find it harder to understand.

Jeff has done a couple of great articles on Cross tabs, which you can read: Cross Tabs and Pivots. If you get stuck, please do provide us with some consumable sample data and expected results (that means as text, in a code block). Also tell us what part of the articles by Jeff you don't understand and we can try to elaborate on it.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Shabbaranks SSC Eights! Points: 880 More actions · Answer 2

Thanks Thom I was trying to structure the data as you mentioned rather than uploading images but the layout was going all cockahoot. Is there a way to insert a table in this forum so I can layout what I'm trying to achieve easier as you mention?

Also would you say Cross Tabs are more for numerical or can you use them for text\strings also?

Thanks again

Thom A SSC Guru Points: 99130 More actions · Answer 3

Make sure you put the data in a code block, otherwise yes the formatting will be lost.

And I recommend a Cross Tab regardless. The type of data (or the data types) doesn't matter.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Shabbaranks SSC Eights! Points: 880 More actions · Answer 4

Thanks again, and you'll have to excuse me my SQL is still at its early stages.

If I use the following query

Select STKTraceItemBatchAttribute.TraceableItemID,
   STKTraceItemBatchAttribute.Title, 
   STKTraceItemBatchAttribute.Value
   From
   STKTraceItemBatchAttribute

I get the following output
TraceableItemID Title         Value
421495         Location        USA
421495         Colour         Silver
421495         BOOKED IN BY    BH
421495         INSPECTED BY N/A
421554         Location UK
421554         Colour         Red
421554         BOOKED IN BY YT
421554         INSPECTED BY N/A
421602         Location Germany
421879         Location Greece
421879         Colour         N/A
421879         BOOKED IN BY RE
421879         INSPECTED BY 
421981         Location France

As you can see from the above data there are multiple TraceableItemId rows

Ideally what I am trying to achieve is one row per TraceableItemId with the additional columns corresponding to the relevant Title's and Values.

As said I must have watched million youtube videos and read various instructions but its just not sinking in.

Thanks

Thom A SSC Guru Points: 99130 More actions · Answer 5

This, therefore, appears to be what you're after (guessing):

WITH YourTable AS(
    SELECT V.TraceableItemID,
           RTRIM(V.Title) AS Title,
           LTRIM(RTRIM(V.[Value])) AS [Value]
    FROM (VALUES(421495,'Location    ', '  USA        '),
                (421495,'Colour       ','      Silver '),
                (421495,'BOOKED IN BY ','   BH        '),
                (421495,'INSPECTED BY ','    N/A      '), --I would recommend NULL over N/A
                (421554,'Location     ',' UK          '),
                (421554,'Colour       ','      Red    '),
                (421554,'BOOKED IN BY ','    YT       '),
                (421554,'INSPECTED BY ','    N/A      '), --I would recommend NULL over N/A
                (421602,'Location     ','Germany      '),
                (421879,'Location     ','Greece       '),
                (421879,'Colour       ','      N/A    '), --I would recommend NULL over N/A
                (421879,'BOOKED IN BY ','    RE       '),
                (421879,'INSPECTED BY ','             '), --Does '' have a different meaning to N/A?
                (421981,'Location     ','France       '))V(TraceableItemID,Title,[Value]))
SELECT YT.TraceableItemID,
       MAX(CASE YT.Title WHEN 'Location' THEN YT.[Value] END) AS [Location],
       MAX(CASE YT.Title WHEN 'Colour' THEN YT.[Value] END) AS Colour,
       MAX(CASE YT.Title WHEN 'BOOKED IN BY' THEN YT.[Value] END) AS BookedInBy,
       MAX(CASE YT.Title WHEN 'INSPECTED BY' THEN YT.[Value] END) AS InspectedBy
FROM YourTable YT
GROUP BY YT.TraceableItemID;

Hopefully the reason you're doing this is to fix your data model, as the way you are storing it at the moment is a bad idea.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Joe Torre SSChampion Points: 10248 More actions · Answer 6

capture

USE test
GO
IF Object_id('STKTraceItemBatchAttribute', 'U') IS NOT NULL
   DROP TABLE STKTraceItemBatchAttribute;
GO
CREATE TABLE STKTraceItemBatchAttribute
   (
     TraceableItemID int
   , Title varchar(50)
   , [Value] varchar(50)
   );
GO
INSERT INTO STKTraceItemBatchAttribute
VALUES (421495, 'Location', 'USA')
     , (421495, 'Colour', 'Silver')
     , (421495, 'BOOKED IN BY', 'BH')
     , (421495, 'INSPECTED BY', 'N/A')
     , (421554, 'Location', 'UK')
     , (421554, 'Colour', 'Red')
     , (421554, 'BOOKED IN BY', 'YT')
     , (421554, 'INSPECTED BY', 'N/A')
     , (421602, 'Location', 'Germany')
     , (421879, 'Location', 'Greece')
     , (421879, 'Colour', 'N/A')
     , (421879, 'BOOKED IN BY', 'RE')
     , (421879, 'INSPECTED BY', '')
     , (421981, 'Location', 'France');
GO

SELECT TraceableItemID
     , [BOOKED IN BY]
     , [Colour]
     , [INSPECTED BY]
     , [Location]
FROM STKTraceItemBatchAttribute
PIVOT (Max([Value]) for title in ([BOOKED IN BY]
     , [Colour]
     , [INSPECTED BY]
     , [Location])
     ) p;
                  
DROP TABLE STKTraceItemBatchAttribute;
GO

Help required creating an SQL PIVOT

Attachments: