Crosstab not returning desired results

Question

Crosstab not returning desired results

andell_ramsay

Valued Member

Points: 71
More actions
July 8, 2009 at 10:36 am

#206677

--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_keyFirstSecondThirdFourth
12345ghiNULLNULLNULL
12345efgNULLNULLNULL
12345defNULLNULLNULL
12345abcNULLNULLNULL
12345NULLxyzNULLNULL
12345NULLwxyNULLNULL
12345NULLNULLlmnNULL
12345NULLNULLNULLtuv
--i need my results to look like this
test_key FirstSecondThirdFourth
12345ghixyzlmntuv
12345efgwxyNULLNULL
12345defNULLNULLNULL
12345abcNULLNULLNULL
How can i get this result set froma crosstab query

Viewing 7 posts - 1 through 6 (of 6 total)

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

Jeff Moden SSC Guru Points: 1004432 More actions · Answer 1

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

andell_ramsay Valued Member Points: 71 More actions · Answer 2

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.

Jeff Moden SSC Guru Points: 1004432 More actions · Answer 3

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

andell_ramsay Valued Member Points: 71 More actions · Answer 4

Thanks this works perfectly. I appreciate your help!

Jeff Moden SSC Guru Points: 1004432 More actions · Answer 5

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

andell_ramsay Valued Member Points: 71 More actions · Answer 6

I cannot take all the credit as I read your article on Forum etiquette before posting. So thanks to you as well.