Cross tab query, without data aggregation
-
Edited for clarity
Hi all im trying to work out how to display the actual results or related records across the page, not their 'sum' or average. I have followed the excellent articles on cross tab queries on this site, but have not found the solution there, as the examples focus on aggregating numerical results.
Heres a sample data table.
USE [Your_Database]
GO
/****** Object: Table [dbo].[Table_3] Script Date: 03/11/2009 01:34:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Table_3](
[Patient] [nchar](10) NULL,
[Sample] [int] NULL,
[Test] [nchar](10) NULL,
[Result] [nchar](10) NULL
) ON [PRIMARY]
GO
Insert Into Table_3 Select Patient='One ', Sample=100, Test='One ', Result='Positive '
Insert Into Table_3 Select Patient='One ', Sample=100, Test='Two ', Result='Positive '
Insert Into Table_3 Select Patient='One ', Sample=100, Test='Three ', Result='Negative '
Insert Into Table_3 Select Patient='One ', Sample=101, Test='One ', Result='Positive '
Insert Into Table_3 Select Patient='One ', Sample=101, Test='Two ', Result='Positive '
Insert Into Table_3 Select Patient='Two ', Sample=102, Test='One ', Result='Negative '
Insert Into Table_3 Select Patient='Two ', Sample=102, Test='Two ', Result='Negative '
Insert Into Table_3 Select Patient='Two ', Sample=102, Test='Three ', Result='Positive '
GO
Here's how i want the results to look.
Patient Sample Test 1 Tes2 Test3
One 100 Positive Negative Negative
One 101 Positive Positive null
Two 102 Negative Negative Positive
Any ideas most welcome.
-
Quick and dirty from what you've got already is to use your existing query as a subquery.
SELECT
company,
year,
sum(q1amt) 'Q1',
sum(q2amt) 'Q2',
sum(q3amt) 'Q3',
sum(q4amt) 'Q4'
FROM (
SELECT Company,
Year,
(CASE WHEN Quarter = 1 THEN Amount ELSE 0 END) AS Q1Amt,
(CASE WHEN Quarter = 2 THEN Amount ELSE 0 END) AS Q2Amt,
(CASE WHEN Quarter = 3 THEN Amount ELSE 0 END) AS Q3Amt,
(CASE WHEN Quarter = 4 THEN Amount ELSE 0 END) AS Q4Amt
FROM SomeTable2
) as t1
GROUP BY
company,
year
ORDER BY
company,
year
-
Change your order by to a group by, and use sum:
SELECT Company,
Year,
SUM(CASE WHEN Quarter = 1 THEN Amount ELSE 0 END) AS Q1Amt,
SUM(CASE WHEN Quarter = 2 THEN Amount ELSE 0 END) AS Q2Amt,
SUM(CASE WHEN Quarter = 3 THEN Amount ELSE 0 END) AS Q3Amt,
SUM(CASE WHEN Quarter = 4 THEN Amount ELSE 0 END) AS Q4Amt
FROM SomeTable2
GROUP BY Company, Year
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
Sorry should have explained more clearly. The 'values' are not values, they are varchar, and they 'cant' be added' and i don't want them added. Just want them to show along side one another, where they are related to the Same Year and company
-
Will there always be exactly one column in the source data with a value? If that is the case you could replace the SUM with MAX.
-
Thanks Richard,
I have tried the max function, but it also requires a numerical field type. FYI, I have updated the original post, to bring some clarity to what i am seeking to achive.
-
SELECT Patient, Sample, MAX(Test1) Test1, MAX(Test2) Test2, MAX(Test3) Test3FROM (
SELECT Patient, Sample,
CASE WHEN Test LIKE 'One%' THEN Result ELSE NULL END Test1,
CASE WHEN Test LIKE 'Two%' THEN Result ELSE NULL END Test2,
CASE WHEN Test LIKE 'Three%' THEN Result ELSE NULL END Test3
FROM Table_3) x
GROUP BY Patient, Sample
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply