Rearranging SQL Data - 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

General SQL Server Forums

New to SQL Server Programming

Rearranging SQL Data

Author

Topic

Rando
Starting Member

11 Posts

Posted - 2010-06-08 : 15:41:23

Hello,

I have some data in a table that I essentially want to query to re-arrange the data for reporting purposes.

Raw Data/Table


Id	MF_ID	SF_ID	UserID	Completed	DateCompleted
16	1	1	20	41		5/12/10
17	1	1	20	40		5/12/10
18	1	2	20	48		5/12/10
19	1	2	20	28		5/12/10

What I want it to look like after querying


Id	MF_ID	Completed_SF_ID1	Completed_SF_ID2	UserID	DateCompleted
20	1	81			76			20	5/12/10

So it's the same data, but displayed in one line to summarize. I'm utterly clueless how to start grouping these.

I'm using TSQL on SQL Server 2008

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-06-08 : 15:47:19

This?

select MF_ID
, sum(case when SF_ID = 1 then Completed else 0 end) as Completed_SF_ID1
, sum(case when SF_ID = 2 then Completed else 0 end) as Completed_SF_ID2
, UserID
, DateCompleted
from MyTable
group by MF_ID, UserID, DateCompleted

Rando
Starting Member

11 Posts

Posted - 2010-06-08 : 15:58:45

That appears to be working. Is there any way to automate the query so it automatically adds the correct amount of columns and SF_IDs? I'd rather not hard code the query to 2 SF_ID and have to change it down the road if we add a few more

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-06-08 : 16:26:08

Add this to vijays code.

, ROW_NUMBER() OVER (ORDER BY MF_ID) + MAX(ID) AS NewID

N 56°04'39.26"
E 12°55'05.63"

Rando
Starting Member

11 Posts

Posted - 2010-06-09 : 08:58:53

Well that would create a new ID for each row returned (which may come in handy some day, thanks!), but I was looking more at trying to make my SQL query variable. Right now I have a total of 6 SF_IDs. There could easily be 10 down the road. I'm wondering if there is a way where I can code the query to account for this and automatically add the new SF_ID columns?

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-06-09 : 09:00:32

Search for Dynamic Cross Tabs here.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources