Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Crosstab not returning desired results Expand / Collapse
Author
Message
Posted Wednesday, July 8, 2009 10:36 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, December 23, 2011 11:38 AM
Points: 15, Visits: 36
--Create a test table as shown below

CREATE TABLE #test_table
(
Test_key decimal(15,0),
question_key decimal(15,0),
result varchar(50)
)
INSERT INTO #test_table
(test_key,question_key,result)
SELECT 12345,567,'abc' union all
SELECT 12345,234,'xyz' union all
SELECT 12345,567,'def' union all
SELECT 12345,567,'ghi' union all
SELECT 12345,234,'wxy' union all
SELECT 12345,123,'lmn' union all
SELECT 12345,789,'tuv' union all
SELECT 12345,123,'lmn' union all
SELECT 12345,567,'efg'
GO

--Run the cross tab query

select distinct test_key,
(CASE question_key when 567 then result end)'First',
(CASE question_key when 234 then result end)'Second',
(CASE question_key when 123 then result end)'Third',
(CASE question_key when 789 then result end)'Fourth'

from #test_table
order by 2 desc, 3 desc, 4 desc

--results shown below

test_key First Second Third Fourth
12345 ghi NULL NULL NULL
12345 efg NULL NULL NULL
12345 def NULL NULL NULL
12345 abc NULL NULL NULL
12345 NULL xyz NULL NULL
12345 NULL wxy NULL NULL
12345 NULL NULL lmn NULL
12345 NULL NULL NULL tuv


--i need my results to look like this
test_key First Second Third Fourth
12345 ghi xyz lmn tuv
12345 efg wxy NULL NULL
12345 def NULL NULL NULL
12345 abc NULL NULL NULL

How can i get this result set froma crosstab query

Post #749482
Posted Sunday, July 19, 2009 11:01 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 6:43 PM
Points: 36,752, Visits: 31,208
It's simple but no one ever answers my questions once I give an answer. With that in mind, please explain the business reason for wanting to do this. Thanks.

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #755495
Posted Monday, July 20, 2009 4:26 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, December 23, 2011 11:38 AM
Points: 15, Visits: 36
The client needs a dump of their data and the way how the tables were designed does not allow for a simple query. The result from the query displays directly in the UI application so the headings have to be created on the fly. The test table is how the data is in the tables by using the cross tab i'm able to traverse the data to fit the requested format for the data dump. Hopes this helps.
Post #756233
Posted Monday, July 20, 2009 6:21 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 6:43 PM
Points: 36,752, Visits: 31,208
Thanks... I appreciate that. Here's the solution... explanation is in the code comments.

--===== This is your original test table with just a bit of reformatting.
-- I'd recommend that you change DECIMAL(15,0) to INT or BIGINT.
CREATE TABLE #Test_Table
(
Test_key DECIMAL(15,0),
Question_Key DECIMAL(15,0),
Result VARCHAR(50)
)
INSERT INTO #Test_Table
(Test_Key, Question_Key, Result)
SELECT 12345, 567, 'abc' UNION ALL
SELECT 12345, 234, 'xyz' UNION ALL
SELECT 12345, 567, 'def' UNION ALL
SELECT 12345, 567, 'ghi' UNION ALL
SELECT 12345, 234, 'wxy' UNION ALL
SELECT 12345, 123, 'lmn' UNION ALL
SELECT 12345, 789, 'tuv' UNION ALL
SELECT 12345, 123, 'lmn' UNION ALL
SELECT 12345, 567, 'efg'
GO
--===== Assuming that the original table cannot be changed,
-- create and populate a new table on the fly. The
-- IDENTITY column will reflect the desired sort order
-- for creating a "Sequence" column. ROW_NUMBER() or
-- "RANK" in SQL Server 2005 would make this a lot easier.
SELECT IDENTITY(INT,1,1) AS RowNum,
Test_Key,
Question_Key,
CAST(0 AS INT) AS Sequence,
Result
INTO #MyWork
FROM #Test_Table
ORDER BY Test_Key, Question_Key, Result DESC
 
--===== Adding this clustered index is critical to getting
-- the ensuing UPDATE to do things in the correct order.
-- It MUST be the clustered index to work correctly.
ALTER TABLE #MyWork
ADD PRIMARY KEY CLUSTERED (RowNum)
WITH FILLFACTOR = 100
 
--===== Declare a couple of obvious named variables...
DECLARE @Prev_Test_Key INT,
@Prev_Question_Key INT,
@Prev_Sequence INT
 
--===== Do the proprietary 3 part update.
-- This works just as if you did it in a loop because,
-- behind the scenes, and UPDATE IS a loop except it's
-- ten's of times faster than a declared loop.
UPDATE #MyWork
SET @Prev_Sequence = Sequence = CASE
WHEN @Prev_Test_Key = Test_Key
AND @Prev_Question_Key = Question_Key
THEN @Prev_Sequence + 1
ELSE 1
END,
@Prev_Test_Key = Test_Key,
@Prev_Question_Key = Question_Key
FROM #MyWork WITH (TABLOCKX)
OPTION (MAXDOP 1)
 
--===== Run the cross tab query to use the new Sequence column
SELECT Test_Key,
MAX(CASE WHEN Question_Key = 567 THEN Result END) AS [First],
MAX(CASE WHEN Question_Key = 234 THEN Result END) AS [Second],
MAX(CASE WHEN Question_Key = 123 THEN Result END) AS [Third],
MAX(CASE WHEN Question_Key = 789 THEN Result END) AS [Fourth]
FROM #MyWork
GROUP BY Test_Key, Sequence

If the original table is a partitioned table, this will still work because of the intermediate #MyWork table we created.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #756276
Posted Tuesday, July 21, 2009 11:09 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, December 23, 2011 11:38 AM
Points: 15, Visits: 36
Thanks this works perfectly. I appreciate your help!
Post #756817
Posted Tuesday, July 21, 2009 2:23 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 6:43 PM
Points: 36,752, Visits: 31,208
You bet. And, I just noticed that you're new to this forum. You did an absolutely outstanding job of providing the data in a readily consumable format on the very first post. Well done and my hat's off to you.

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #756949
Posted Thursday, July 23, 2009 12:51 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, December 23, 2011 11:38 AM
Points: 15, Visits: 36
I cannot take all the credit as I read your article on Forum etiquette before posting. So thanks to you as well.
Post #758514
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse