T-SQL select multiple rows into 1 row

Hi All,

Quick question how do I select multiple rows and put them into one row.  I have 1 table that collects data at the end of the week and I need to create a report that reflects the whole month.  For example

test table

ID    Year Month Week TotalGross
102 2015   1           1        100
102 2015   1           2         50
102 2015   1           3         50
102 2015   1           4         25
102 2015   1           5          0

Needs to return

ID    Year Month Week1 Week2 Week3 Week4 Week5 Total
102  2015     1        100       50         50         25        0          225

Thank you all for your help in advance
thomasm1948Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mike EghtebasDatabase and Application DeveloperCommented:
;with cte as
(Select ID, Year, Month,  Week, TotalGross, SUM(TotalGross) OVER ( ) AS total
from #T
Where Week<6)
Select *   
From cte
Pivot (Sum(TotalGross) 
For [Week] in ([1],[2],[3],[4],[5])) as pvt;

Open in new window


2nd edit...  tested, it works
thomasm1948Author Commented:
I have an issue with my SQL it tells me that I have an error in the syntex

; with cte as
Mike EghtebasDatabase and Application DeveloperCommented:
The solution was revised a few times. Please try the latest, it works. BTW make sure to change table name #T with whatever table name you have.
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

thomasm1948Author Commented:
I get an error if I start with a ; in mysql 5.6
Mike EghtebasDatabase and Application DeveloperCommented:
I am sorry this was for SQL Server because you have included SQL Server 2008. Haven't done much in MySQL.

Check out:  http://www.artfulsoftware.com/infotree/queries.php#78

see lower left corner of the white box for the link
thomasm1948Author Commented:
I figured it out

select id, year, month,
 
  max(case when week =1 then cast(ToTalGross as decimal(30,2)) else 0 end) week1,
  max(case when week =2 then cast(ToTalGross as decimal(30,2)) else 0 end) week2,
  max(case when week =3 then cast(ToTalGross as decimal(30,2)) else 0 end) week3,
  max(case when week =4 then cast(ToTalGross as decimal(30,2)) else 0 end) week4,
  max(case when week =5 then cast(ToTalGross as decimal(30,2)) else 0 end) week5
   
from
   test1  
group by
  ID
Mike EghtebasDatabase and Application DeveloperCommented:
Very nice. You also gave me a chance to practice it in SQL Server.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
thomasm1948Author Commented:
Thank you for your help
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.