Changing result set

Question

Changing result set

Angel Huerta

SSC-Addicted

Points: 460
More actions
July 10, 2007 at 12:01 pm

#117646

Hi, everyone.
I have a table (well, actually is a result set from a SP) like this:
StoreIDArtIdDescStatusVal
------------------------------------
10001XXXXA1
10002YYYYB2
10011XXXXC3
10012YYYYB3
10021XXXXC2
10022YYYYB1
but I need to get the data like this:
ArtIdDescStatus1000Val1000Status1001Val1001Status1002Val1002
---------------------------------------------------------------------------------------
1XXXXA1C3C1
2YYYYB2B3B1
One of the main problems I have is that the user can select one or more (up to 30) store Id's.
Does anyone has some suggestions?
Thanks!

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

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

David McFarland SSChampion Points: 11815 More actions · Answer 1

Since the number of Val and Status columns is dynamic, my first inclination is to tell you that it will take dynamic SQL (if it were fixed, you could do a bunch of CASE statements). Before you head down that path, I need to ask why you need the data like this? If it's for reporting, then I'd let the report tool itself handle it, as most of them can handle crosstab/pivot type issues for you, without changing your data at all. In Reporting Services, for example, your data would work like it is currently, if you used a Matrix data region.

Mark Harr SSCrazy Eights Points: 9921 More actions · Answer 2

2 options:

Option 1, have your application construct a dynamic SQL statement, using Case constructs for each column for each store:

SELECT ArtID, Desc,
CASE StoreID WHEN 1000 THEN Status ELSE Null END as Status1000,
CASE StoreID WHEN 1000 THEN Val ELSE Null END as Val1000,
CASE StoreID WHEN 1001 THEN Status ELSE Null END as Status1001,
CASE StoreID WHEN 1002 THEN Val ELSE Null END as Val1001
FROM MyTable
WHERE StoreID in (1000,1001)

In your app, repeat each set of Case statements for each store you want in the query, as well as constructing the WHERE clause in separate strings.

Option 2, pass the list of StoreIDs to stored proc, either as delimited string or XML, then construct temporary table in sproc with columns, do separate queries to load the temp table, then do a SELECT query of the final temp table.

Hope this helps

Mark

Angel Huerta SSC-Addicted Points: 460 More actions · Answer 3

I'm eliminating a couple of big embedded cursors from this SP, and I don't have access to the source code of this old app (it doesn't use any report tool). So, in order to leave the app functional I must put the output like it was in the old SP.

Th old SP created a temporary table with dynamic SQL and it was filled within 2 embedded another cursor. I've changed the SP with some joins, but now I have the table in the format I listed, so thats when I came asking for help =D

David Jackson SSCertifiable Points: 6414 More actions · Answer 4

I seem to post this a lot

Look at this thread, I had a very similar problem, which was soon worked out.

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=242919

HTH

Dave J

http://glossopian.co.uk/
"I don't know what I don't know."