Help required creating an SQL PIVOT

  • 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.
  • 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

  • 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

  • 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

  • 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

  • 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

  • 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

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

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