CODEWITHSUNDEEP
sqlsql-serverpivot
Nick
Nick

Reputation: 2907

SQL query - turn cell into column

I have a query that returns items like this

Item    --  Code   --  Thing
------------------------------
Item A  --  Code A --  Thing 1
Item A  --  Code A --  Thing 2
Item A  --  Code A --  Thing 3
Item A  --  Code A --  Thing 4
Item B  --  Code B --  Thing x
Item B  --  Code B --  Thing y
Item C  --  Code C --  Thing z
Item C  --  Code C --  Thing a
Item C  --  Code C --  Thing b
Item C  --  Code C --  Thing c

And I want to turn this into something like this

Item    --  Code   --  Thing 1 -- Thing 2 -- Thing 3 -- Thing 4 -- Thing 5
---------------------------------------------------------------------------
Item A  --  Code A --  Thing 1 -- Thing 2 -- Thing 3 -- Thing 4 -- NULL
Item B  --  Code B --  Thing x -- Thing y -- NULL    -- NULL    -- NULL
Item C  --  Code C --  Thing a -- Thing b -- Thing c -- Thing d -- NULL

Where any item over 5 can be ignored.

Update:

By adding "ROW_NUMBER() over (Partition by Table.Id order by Table2.Id)" In my query I now get:

Item    --  Code   --  Thing  -- Index
---------------------------------------
Item A  --  Code A --  Thing 1 -- 1
Item A  --  Code A --  Thing 2 -- 2
Item A  --  Code A --  Thing 3 -- 3
Item A  --  Code A --  Thing 4 -- 4
Item B  --  Code B --  Thing x -- 1
Item B  --  Code B --  Thing y -- 2
Item C  --  Code C --  Thing z -- 1
Item C  --  Code C --  Thing a -- 2
Item C  --  Code C --  Thing b -- 3
Item C  --  Code C --  Thing c -- 4

Which allows me to use the Pivot function and change the data accordingly. Still working on that so any help is very appreciated.

Upvotes: 0

Views: 1803

Answers (2)

Marty
Marty

Reputation: 3555

You can Use PIVOT Operator

Here's a sample query for a similar problem 

SELECT *
FROM
    (
    SELECT Contact_Id AS CT
          , [Age]
          , [Sex]
          , [State]
          , [Country]
          , [Keyword]
          , [Married]
          , [Kids]
          , [Car]
     FROM
         (SELECT c.PropertyName
               , c.ValueString
               , c.Contact_Id
          FROM
              ContactProfiles c) AS ctp
         PIVOT (max(ctp.ValueString) FOR PropertyName IN ([Age], [Sex], [State], [Country], [Keyword], [Married], [Kids], [Car])) AS PivotTable
         ) AS pvt

WHERE
    pvt.[Age] > 18

Upvotes: 1

KMB
KMB

Reputation: 162

(First try that didn't work) One clean way to code this is:

    SELECT
        Item,
        Code,
        Thing1 = Case When Thing = 'Thing 1' Then 'Thing 1' Else Null End,
        Thing2 = Case When Thing = 'Thing 2' Then 'Thing 2' Else Null End,
        Thing3 = Case When Thing = 'Thing 3' Then 'Thing 3' Else Null End,
        Thing4 = Case When Thing = 'Thing 4' Then 'Thing 4' Else Null End,
        Thing5 = Case When Thing = 'Thing 5' Then 'Thing 5' Else Null End
   FROM [Items]
   WHERE Thing BETWEEN 'Thing 1' AND 'Thing 5' -- preselection should improve performance
   GROUP BY 
        Item,
        Code,
        Thing1 = Case When Thing = 'Thing 1' Then 'Thing 1' Else Null End,
        Thing2 = Case When Thing = 'Thing 2' Then 'Thing 2' Else Null End,
        Thing3 = Case When Thing = 'Thing 3' Then 'Thing 3' Else Null End,
        Thing4 = Case When Thing = 'Thing 4' Then 'Thing 4' Else Null End,
        Thing5 = Case When Thing = 'Thing 5' Then 'Thing 5' Else Null End

Works because you have a known, limited number of columns to pivot. The PIVOT function is another way but I am not familiar with this one.

(Second try) This works!

    DECLARE @Items TABLE
    (
        Item    char(1),
        Code    char(1),
        Thing   char(1)
    )
    INSERT INTO @Items
    SELECT 'A', 'A', '1'
    UNION
    SELECT 'A', 'A', '2'
    UNION
    SELECT 'A', 'A', '3'
    UNION
    SELECT 'A', 'A', '4'
    UNION
    SELECT 'B', 'B', 'x'
    UNION
    SELECT 'B', 'B', 'y'
    UNION
    SELECT 'C', 'C', 'f'
    UNION
    SELECT 'C', 'C', 'g'
    UNION
    SELECT 'C', 'C', 'h'
    UNION 
    SELECT 'C', 'C', 'j'

    SELECT
        Items.Item,
        Items.Code,
        Thing1 = Max(Case When OrderedItems.ThingPlace = 1 Then OrderedItems.Thing Else Null End),
        Thing2 = Max(Case When OrderedItems.ThingPlace = 2 Then OrderedItems.Thing Else Null End),
        Thing3 = Max(Case When OrderedItems.ThingPlace = 3 Then OrderedItems.Thing Else Null End),
        Thing4 = Max(Case When OrderedItems.ThingPlace = 4 Then OrderedItems.Thing Else Null End),
        Thing5 = Max(Case When OrderedItems.ThingPlace = 5 Then OrderedItems.Thing Else Null End)
    FROM @Items Items
        LEFT OUTER JOIN 
        (
            SELECT
                Code, Thing, 
                ThingPlace = Row_Number() OVER (PARTITION BY Code ORDER BY Thing)
            FROM @Items
            GROUP BY Code, Thing
        ) OrderedItems
            ON OrderedItems.Code = Items.Code
    WHERE OrderedItems.ThingPlace Is Null OR OrderedItems.ThingPlace <= 5
    GROUP BY
        Items.Item,
        Items.Code

Result:

A A 1 2 3 4 NULL

B B x y NULL NULL NULL

C C f g h j NULL

The trick was to build an ordered list of things first. I used only Code for the lookup as this seems in this case with the data given to be the key (Item didn't really matter). You might have to extend the join connection. Using the ordered list I then exclude all Thing(s) that are above the sixth place in order. This only works if Thing(s) is actually a natural order - otherwise it's back to square one to find out first what Thing(s) is.

Upvotes: 0

Related Questions