Is this possible? - SQL Server Forums

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

SQL Server 2012 Forums

Transact-SQL (2012)

Is this possible?

Author

Topic

natg504
Starting Member

14 Posts

Posted - 2014-02-06 : 10:19:15

I have two tables…

Table 1: DataSeries

SeriesID, SeriesName
----------------------------
1, SeriesOneName
2, SeriesTwoName
3, SeriesThreeName
4, SeriesFourName

Table 2 : Data

ID, DatePosted, SeriesID, Value
-----------------------------------
1, 2013-01-31, 3, 382
2, 2013-01-31, 4, 578
3, 2013-02-01, 1, 123
4, 2013-02-01, 2, 456
5, 2013-02-01, 3, 382
6, 2013-02-01, 4, 578
...

I’d like to figure out a way to do a query where I can have the data in a format more like this:

DatePosted, SeriesOneName, SeriesTwoName, SeriesThreeName, SeriesFourName
-----------------------------------------------------------------------------
2013-01-31, NULL, NULL, 382, 578
2013-02-01, 123, 456, 382, 578

Is there any way to write a query or a couple of queries that would do that?

Thanks!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-02-06 : 16:43:54

[code]SELECT DatePosted,
MAX(CASE WHEN SeriesID = 1 THEN Value ELSE NULL END) AS Series1Name,
MAX(CASE WHEN SeriesID = 2 THEN Value ELSE NULL END) AS Series2Name,
MAX(CASE WHEN SeriesID = 3 THEN Value ELSE NULL END) AS Series3Name,
MAX(CASE WHEN SeriesID = 4 THEN Value ELSE NULL END) AS Series4Name
GROUP BY DatePosted
ORDER BY DatePosted;[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources