SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Crosstab not returning desired results


Crosstab not returning desired results

Author
Message
andell_ramsay
andell_ramsay
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (379K reputation)SSC Guru (379K reputation)SSC Guru (379K reputation)SSC Guru (379K reputation)SSC Guru (379K reputation)SSC Guru (379K reputation)SSC Guru (379K reputation)SSC Guru (379K reputation)

Group: General Forum Members
Points: 379239 Visits: 42943
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
andell_ramsay
andell_ramsay
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
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.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (379K reputation)SSC Guru (379K reputation)SSC Guru (379K reputation)SSC Guru (379K reputation)SSC Guru (379K reputation)SSC Guru (379K reputation)SSC Guru (379K reputation)SSC Guru (379K reputation)

Group: General Forum Members
Points: 379239 Visits: 42943
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
andell_ramsay
andell_ramsay
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 36
Thanks this works perfectly. I appreciate your help!
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (379K reputation)SSC Guru (379K reputation)SSC Guru (379K reputation)SSC Guru (379K reputation)SSC Guru (379K reputation)SSC Guru (379K reputation)SSC Guru (379K reputation)SSC Guru (379K reputation)

Group: General Forum Members
Points: 379239 Visits: 42943
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
andell_ramsay
andell_ramsay
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search