Get all data into a single row

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

Get all data into a single row

Author

Topic

hk13
Starting Member

12 Posts

Posted - 2015-05-06 : 14:40:03

Hello,

I thought I was finally figuring out this SQL stuff, but I'm not sure what I'm doing wrong here. I'm trying to get all this data into a single row, but instead I'm getting results like a staircase with multiple rows. What concept am I missing here?

I feel like it's something really simple, but I'm just having a brain fart and can't think. Thanks in advance for any help.

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2015-05-06 : 14:45:18

you can put a max() around each sub query and lose udfmonth/year from your group by. you could also use pivot if the months are always the same.

https://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2015-05-07 : 01:48:46

Check the SQL COALESCE function - http://www.sqlserver-dba.com/2012/09/sql-server-create-a-comma-delimited-sql-result-set.html

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2015-05-07 : 03:38:00

GROUP BY SRB.udfSalesRepID

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2015-05-07 : 03:42:56

Also, your original query does not have a correlation between SalesRepID and the budget sum.
Try this for fun

SELECT		udfSalesRepID,
		SUM(CASE WHEN udfYear = 2015 AND udfMonth =  3 THEN udfSalesBudgetAmount ELSE 0 END) AS [Mar],
		SUM(CASE WHEN udfYear = 2015 AND udfMonth =  4 THEN udfSalesBudgetAmount ELSE 0 END) AS [Apr],
		SUM(CASE WHEN udfYear = 2015 AND udfMonth =  5 THEN udfSalesBudgetAmount ELSE 0 END) AS [May],
		SUM(CASE WHEN udfYear = 2015 AND udfMonth =  6 THEN udfSalesBudgetAmount ELSE 0 END) AS [Jun],
		SUM(CASE WHEN udfYear = 2015 AND udfMonth =  7 THEN udfSalesBudgetAmount ELSE 0 END) AS [Jul],
		SUM(CASE WHEN udfYear = 2015 AND udfMonth =  8 THEN udfSalesBudgetAmount ELSE 0 END) AS [Aug],
		SUM(CASE WHEN udfYear = 2015 AND udfMonth =  9 THEN udfSalesBudgetAmount ELSE 0 END) AS [Sep],
		SUM(CASE WHEN udfYear = 2015 AND udfMonth = 10 THEN udfSalesBudgetAmount ELSE 0 END) AS [Oct],
		SUM(CASE WHEN udfYear = 2015 AND udfMonth = 11 THEN udfSalesBudgetAmount ELSE 0 END) AS [Nov],
		SUM(CASE WHEN udfYear = 2015 AND udfMonth = 12 THEN udfSalesBudgetAmount ELSE 0 END) AS [Dec],
		SUM(CASE WHEN udfYear = 2016 AND udfMonth =  1 THEN udfSalesBudgetAmount ELSE 0 END) AS [Jan],
		SUM(CASE WHEN udfYear = 2016 AND udfMonth =  2 THEN udfSalesBudgetAmount ELSE 0 END) AS [Feb]
FROM		dbo.udoSalesRepBudget
GROUP BY	udfSalesRepID;

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

hk13
Starting Member

12 Posts

Posted - 2015-05-07 : 09:25:53

Wow, thank you SwePeso. That works perfectly and is way cleaner than what I had.

I was trying to figure out pivot yesterday, but none of my trials seemed to work. Your method makes a lot more sense to me. Thanks again.

quote:

Originally posted by SwePeso

Also, your original query does not have a correlation between SalesRepID and the budget sum.
Try this for fun

SELECT		udfSalesRepID,
		SUM(CASE WHEN udfYear = 2015 AND udfMonth =  3 THEN udfSalesBudgetAmount ELSE 0 END) AS [Mar],
		SUM(CASE WHEN udfYear = 2015 AND udfMonth =  4 THEN udfSalesBudgetAmount ELSE 0 END) AS [Apr],
		SUM(CASE WHEN udfYear = 2015 AND udfMonth =  5 THEN udfSalesBudgetAmount ELSE 0 END) AS [May],
		SUM(CASE WHEN udfYear = 2015 AND udfMonth =  6 THEN udfSalesBudgetAmount ELSE 0 END) AS [Jun],
		SUM(CASE WHEN udfYear = 2015 AND udfMonth =  7 THEN udfSalesBudgetAmount ELSE 0 END) AS [Jul],
		SUM(CASE WHEN udfYear = 2015 AND udfMonth =  8 THEN udfSalesBudgetAmount ELSE 0 END) AS [Aug],
		SUM(CASE WHEN udfYear = 2015 AND udfMonth =  9 THEN udfSalesBudgetAmount ELSE 0 END) AS [Sep],
		SUM(CASE WHEN udfYear = 2015 AND udfMonth = 10 THEN udfSalesBudgetAmount ELSE 0 END) AS [Oct],
		SUM(CASE WHEN udfYear = 2015 AND udfMonth = 11 THEN udfSalesBudgetAmount ELSE 0 END) AS [Nov],
		SUM(CASE WHEN udfYear = 2015 AND udfMonth = 12 THEN udfSalesBudgetAmount ELSE 0 END) AS [Dec],
		SUM(CASE WHEN udfYear = 2016 AND udfMonth =  1 THEN udfSalesBudgetAmount ELSE 0 END) AS [Jan],
		SUM(CASE WHEN udfYear = 2016 AND udfMonth =  2 THEN udfSalesBudgetAmount ELSE 0 END) AS [Feb]
FROM		dbo.udoSalesRepBudget
GROUP BY	udfSalesRepID;

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