March 15, 2009 at 7:01 pm
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.
March 15, 2009 at 9:14 pm
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
March 16, 2009 at 3:46 pm
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...
March 16, 2009 at 4:08 pm
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
March 17, 2009 at 6:19 am
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