May 20, 2003 at 8:14 pm
This is easy, but trivial.
I need the top table which is identical to a table in my database to be formatted like the bottom table. Some have suggested Group By, Append and a few others - but i can't quite figure it out.
The top table has more "Stores" with similiar related data - the traited/valid columns will in a way need to be dynamically generated by SQL b/c i won't know for sure how many stores i will need to query against every time. THe user will choose anywhere from 0 to 50 STores from a checkbox list and they will need to know traited/valid for all of them and displayed like the bottom table.
Any help would be greatly appreciated.
Thanks
May 21, 2003 at 1:59 am
It looks like you need a query for each Store left joined to each other on ItemNum To look like
Select * from
(
Select ItemNum, S1_Traited, S1_Valid
from SourceTable
Where Store = 1
) as a
Left Join
(
Select ItemNum, S2_Traited, S2_Valid
from SourceTable
Where Store = 2
) as b
on a.itemnum = b.itemnum
Alternatively
Select ItemNum, S1_Traited, S1_Valid
from SourceTable as a
Left join Sourcetable as b
on b.itemnum = a.itemnum
And b.store = 2
Where a.Store = 1
Your Top row of the output table will have to be created in the web app as the results are only fron row two.
Hope this helps
May 21, 2003 at 3:32 am
This is called a pivot table.
If you know the number of items you are going to pivot then it is as simple as
SELECT
ItemNum,
SUM(CASE Store WHEN 1 Then Traited ELSE 0 END) Traited1,
SUM(CASE Store WHEN 1 Then Valid ELSE 0 END) Valid1,
SUM(CASE Store WHEN 2 Then Traited ELSE 0 END) Traited2,
SUM(CASE Store WHEN 2 Then Valid ELSE 0 END) Valid2
FROM tblX
Group By
ItemNum
But since you said the user will choose from 1 to 50 stores it will be best for you to build the above dynamically for submission.
Or even better OLAP offers a pivot feature which might make the whole thing much simpler for you. Sorry I have not had the opportunity to get ours up and running so hopefully someone else can throw in here.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply