Help with Converting Rows to Columns? PLEASE? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help with Converting Rows to Columns? PLEASE?

Author

Topic

alliemac9
Starting Member

4 Posts

Posted - 2010-04-06 : 18:32:30

I Googled this, then played in Access, then talked to my husband (who uses SQL a fair bit), then downloaded SQL Server Express 2008 and feel like I'm testing lots of different paths with no luck and am not sure where to focus next.

Basically, I have an enormous database where each event (identified by combination of two columns: date and location) has a test and a result. So, for each event, you may have ten rows of results for Test A, Test B, etc like this:

Date Location Test Result
4/3/10 Park A 12
4/3/10 Park B 880
4/3/10 Park C 0.3
4/3/10 Road B 16
4/4/10 Park D 47

Instead, I would like to have the results displayed as:

Date Location Result A Result B Result C Result D
4/3/10 Park 12 880 0.3 null
4/3/10 Road null 16 null null
4/4/10 Park null null null 47

Here's where I've ended up and am not sure if I am on the right track or should scrap and begin again a different way...

SELECT C.date, C.location,
JtestA.result, JtestB.result, JtestC.result, JtestD.result,

FROM table AS C
LEFT JOIN table AS JtestA ON C.location = JtestA.location AND C.date = JtestA.date
LEFT JOIN table AS JtestB ON C.location = JtestB.location AND C.date = JtestB.date
LEFT JOIN table AS JtestC ON C.location = JtestC.location AND C.date = JtestC.date
LEFT JOIN table AS JtestD ON C.location = JtestD.location AND C.date = JtestD.date

WHERE JtestA.test='A'
AND JtestB.test='B'
AND JtestC.test='C'
AND JtestD.test='D'

GROUP BY C.date, C.location
ORDER BY C.date, C.location;

The GROUP BY is definitely causing problems as I don't have an aggregate function, etc. I'm just trying to get one record per "event" with all the data in new columns. My actual dataset has more than one result per "test" that needs to be carried through, but I think I could handle that upscaling if I knew where to go from here.

HELP??!?!?!??

alliemac9
Starting Member

4 Posts

Posted - 2010-04-06 : 18:37:49

Sorry, I tried to make the columns line up with spaces - that obviously didn't work. I'll repeat the "tables" to hopefully make it easier to view.

EXISTING TABLE

Date____Location____Test____Result
4/3/10__Park________A_______12
4/3/10__Park________B_______880
4/3/10__Park________C_______0.3
4/3/10__Road________B_______16
4/4/10__Park________D_______47

OUTPUT TABLE I NEED:

Date____Location____Result A____Result B____Result C___Result D
4/3/10__Park________12__________880_________0.3________null
4/3/10__Road________null________16__________null_______null
4/4/10__Park________null________null________null_______47

alliemac9
Starting Member

4 Posts

Posted - 2010-04-06 : 18:57:46

And in case it makes a difference, ideally I'd like this to be a query that can be re-run at any point in time to capture the updates to the database. The results will be exported as a table for use in a GIS by someone else.

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-06 : 19:14:56

Try this:


CREATE TABLE #tmp (
	Date DATETIME,
	Location VARCHAR(50),
	Test VARCHAR(50),
	Result DECIMAL(8, 2)
)

INSERT INTO #tmp
SELECT '2010-04-03', 'Park', 'A', 12
UNION ALL SELECT '2010-04-03', 'Park', 'B', 880
UNION ALL SELECT '2010-04-03', 'Park', 'C', 0.3
UNION ALL SELECT '2010-04-03', 'Road', 'B', 16
UNION ALL SELECT '2010-04-04', 'Park', 'D', 47
	
SELECT Date, Location, A, B, C, D
FROM #tmp
PIVOT ( SUM(Result) FOR Test 
	IN (A, B, C, D )
) AS p
ORDER BY Date, Location

DROP TABLE #tmp

The bit you're interested in is in bold. The rest just sets up some dummy data. You'll need to change the table name.

There are 10 types of people in the world, those that understand binary, and those that don't.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-07 : 00:11:18

quote:
Originally posted by alliemac9

And in case it makes a difference, ideally I'd like this to be a query that can be re-run at any point in time to capture the updates to the database. The results will be exported as a table for use in a GIS by someone else.

Can there was other values in test column other than A to D? In that case do you need them to be displayed into column as well?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

alliemac9
Starting Member

4 Posts

Posted - 2010-04-07 : 11:10:11

Yes, there are about 10 different "tests" that were done at each location on each date. And there are 6 columns of information per test that I am attempting to carry along to the new table. So, instead of just a "Result A" column, I would have "Result A", "Data Qualifier A", "Boolean A", etc. and the same for each of the 10 tests.

So, in theory, I would have 2 columns identifying the event (date/location) and then 60 (10x6) columns providing the results for that event. Note that there are some columns in the original table that I am not pulling forward into the new table.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-07 : 12:41:10

if number of values to pivot is not certain use dynamic pivot

http://beyondrelational.com/blogs/madhivanan/archive/2007/08/27/dynamic-crosstab-with-multiple-pivot-columns.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources