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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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

Go to Top of Page
   

- Advertisement -