Pivot table display

Question

HI,

I am having data as shown below

ItemCode      Attr1    Attr1  InStock
ITEM-000001   43      S      1
ITEM-000001   52      L    2
ITEM-000006   42      R    1
ITEM-000006   44      R    2
ITEM-000009   56      R    1
ITEM-000011   40      R    2

And by using Pivot table (or any other technique) i want the reslut like as

Output for ITEM-000001
     43   52

S    1    
L         2

Same for others.

Kind regards, Om

Answer 1

I would suggest you rather write your query to go for the following layout, and use a reporting tool to finalise the output as you have shown. Most reporting tools should have little trouble pulling the ItemCode column into a section heading.

                   42   43   44   52   56
ITEM-000001   S         1
ITEM-000001   L                   2
ITEM-000006   R    1         2
ITEM-000009   R                         1

The query to create the above layout is as follows:

SELECT  ItemCode, Attr2,
        SUM(CASE WHEN Attr1=42 THEN InStock ELSE 0 END) AS S42,
        SUM(CASE WHEN Attr1=43 THEN InStock ELSE 0 END) AS S43,
        SUM(CASE WHEN Attr1=44 THEN InStock ELSE 0 END) AS S44,
        SUM(CASE WHEN Attr1=52 THEN InStock ELSE 0 END) AS S52,
        SUM(CASE WHEN Attr1=56 THEN InStock ELSE 0 END) AS S56
FROM    StockData
GROUP BY ItemCode, Attr2

EDIT

If there is insufficient consistency in the values of Attr1 and Attr2 to perform a standard pivot on either of those columns, then SQL is not the best tool to solve your problem.
You'll be better off returning the data to a client application, and allowing it to process the information for you. You'll be able to far more easily add arbitrary columns to your result before getting to final out.