Pivot a table

  • Hi,

    I have a table, where there are stores.

    Stores

    StoreId Name

    1 Store 1

    2 Store 2

    3 Store 3

    In other table, there are Items.

    Items

    Id Name

    1 Item 1

    2 Item 2

    3 Item 3

    4 Item 4

    And in other table there are the shopping by item and store

    Buys

    Id StoreId ItemId Amount

    1 1 2 30000

    1 2 3 20000

    1 3 4 10000

    1 1 4 1000

    I need report:

    Item 1 Item2 Item3 Item 4

    Store 1 0 30000 0 1000

    Store 2 0 0 20000 0

    Store 3 0 0 0 10000

    How can I do it, If the stores and items are dinamics?.

    According to my understanding, I can PIVOT the table Items, but with defined Items, and it isn't possible.

    Thanks.

  • You are correct that you have to define the columns in advance for PIVOT. This isn't a problem for [storeName], but it is for [itemName] because your number of columns will vary with the number of items in the items table. So you must dynamically create a string for the query you need, then run the query using sp_executeSQL.

    The following code will accomplish the result you want by using CASE statements instead of a PIVOT. It will also run fairly quickly. I am breaking the code into separate blocks to separate the building of the sample data from the processing, for readability. But all three blocks should be run together as a unit.

    First the code takes totals by store and item and stores them in a temporary table. Then it builds the query string @sql, with a line for each column that will represent a distinct item. (The code involving the [storeName] column is constant.) Finally, it executes the query string which has been stored in @sql

    NOTE: It is not necessary to do this in two steps. A summary query can be written dynamically to do it all in one step, and will probably run faster. However I feel it is easier for you to grasp the concept if you can see the contents of #temp, and then look at the code that "pivots" it. Also, if you only wanted your report to show items that had actually sold at one of your stores, you could build a distinct list of item names from the #temp table, instead of @Items.

    As you will see, it's really fairly simple to understand. When approaching these problems, I first write the query that will give the desired results, then write a query that will produce the exact characters of that query. Please let me know if you have any questions.

    Bob

    -----------------------------------------------------------------------------------------------

    -- queries to set up sample data

    -----------------------------------------------------------------------------------------------

    declare @Stores table (StoreID int, StoreName varchar(20))

    declare @SQL nvarchar(4000)

    insert into @Stores

    select 1, 'Store 1' union all

    select 2, 'Store 2' union all

    select 3, 'Store 3'

    declare @items table (itemID int, itemName varchar(20))

    insert into @items

    select 1, 'Item 1' union all

    select 2, 'Item 2' union all

    select 3, 'Item 3' union all

    select 4, 'Item 4'

    declare @sales table (salesId int, StoreId int, ItemId int, salesAmount int)

    insert into @sales

    select 1, 1, 2, 30000 union all

    select 1, 2, 3, 20000 union all

    select 1, 3, 4, 10000 union all

    select 1, 1, 4, 1000

    -----------------------------------------------------------------------------------------------

    -- summary query by store and item to create #temp

    -----------------------------------------------------------------------------------------------

    select storeName, itemName, sum(salesAmount) as salesAmount

    into #temp

    from @sales s1

    join @stores s2 on s2.storeID = s1.storeid

    join @items i1 on i1.itemID = s1.itemID

    group by storeName,itemName

    order by storeName,itemName

    select * from #temp

    -----------------------------------------------------------------------------------------------

    -- dynamic SQL query to build sales totals by store/item from #temp

    -- code for store name is constant, generate one column for each item name

    -- char(13) needed only to force line breaks for readability when @SQL is printed

    -----------------------------------------------------------------------------------------------

    set @SQL = 'SELECT storeName as [Store Name]'+char(13)

    select @SQL = @SQL+ replace(' ,sum(case when itemName = ''~item~'' then salesAmount else 0 end) as [~item~]','~item~', itemName)+char(13)

    from @items

    order by itemName

    set @SQL = @SQL + 'FROM #temp'+char(13)+'GROUP BY storeName'+char(13)+'ORDER BY storeName'

    print @SQL -- So you can see what is generated. Remove this line in production.

    exec sp_executeSQL @SQL

    drop table #temp

    edited to break code into blocks for readability

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • Ok, thanks....

    But now, my problem is:

    I have to save this information in a text file, then i need know, which item has been sold.

    ie I want that the first row will be:

    Store Item 1 Item 2 Item 3 Item 4

    The next rows,

    Store 1 0 0 0 0

    Store 2 1000 20000 0 0

    How can I do it?

    Thanks,.... very thanks so much...

  • There are a couple of ways.

    First, if you are running this from SQL Enterprise Manager, there is an option to include row headers when you send your query output to a file (Results to File). In the dropdown menus, look under Tools\Options\Query Results\Results to Text.

    If you actually want to make your column header text appear as rows when the query runs, say from an external application, you can still do it, but I'm going to make you think. 😉

    First we want another row of constant information to appear at the front of the rows we're already producing. Seems like a UNION ALL query might be good enough to add constant text.

    SELECT something.... FROM somewhere

    UNION ALL

    the existing query

    So write that query. Not dynamically... just take the output from the print statement and add a query at the top with UNION ALL to get the output you want from #temp.

    After you've done that, think about how the technique I just gave you can be used to pull the column names and build an additional string for the query you are adding with a UNION ALL.

    Try that first. If you run into problems, just post your code here and I will help you over the rough spots.

    If you can work through this one time for yourself, you will OWN this technique. Don't think it's hard. I'm certain you can do it.

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • Thanks, but you don't understand me.

    I need taht the first row have all the ITEMS table records as columns and not as rows. Eg, the table Items

    Id Name

    1 Item 1

    2 Item 2

    3 Item 3

    If i use select * from items, the items are rows and i need that my query as result:

    item1 item2 item 3

    Thanks again,

Viewing 5 posts - 1 through 5 (of 5 total)

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