Changing Table Columns - 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

Changing Table Columns

Author

Topic

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 2008-08-06 : 02:04:08

I have this table:

Call Name Score
1 Michael 89
2 Lyka 90
1 Mark 78
1 Lyka 98
2 Mark 78
2 Prince 89
3 Prince 90
3 Lyka 78
3 Mark 98

I want to covert it to this:

Call Michael Mark Prince Lyka
1 89 78 98
2 78 89 90
3 98 90 78

Is it possible?

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 2008-08-06 : 02:37:51

basically it's converting column into row

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 2008-08-06 : 02:51:44

any help?

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-08-06 : 03:07:55

2000 or 2005? if you search the forum there are loads of examples of how to do to this.

Em

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 2008-08-06 : 03:09:15

it's 2000

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-08-06 : 03:13:09

then use CASE. i'll start you off...

select sum(case when [NAME] = 'Michael' then [Score] else 0 end) as [Michael]

Em

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 2008-08-06 : 03:20:58

ok i got it..it resulted it with 178.

It should be

Call Michael
1 89
2
3 89

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 2008-08-06 : 03:30:39

I think I'm getting it:

SELECT call, SUM(CASE WHEN [NAME] = 'Michael' THEN [Score] ELSE 0 END) AS [Michael]
FROM dbo.JP
GROUP BY call

However, my concern is, what if a new name comes in. Is there a way that it will automatically compute it?

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 2008-08-06 : 04:10:13

can anyone help me? thanks

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 2008-08-06 : 05:27:25

anyone?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-06 : 05:35:26

quote:
Is there a way that it will automatically compute it?

Use Dynamic SQL http://www.sommarskog.se/dynamic_sql.html

KH
[spoiler]Time is always against us[/spoiler]

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 2008-08-06 : 05:38:51

can you tell me how?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-06 : 05:41:08

Basically you have to generate your query dynamically. Read the link I posted.

KH
[spoiler]Time is always against us[/spoiler]

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 2008-08-06 : 05:44:28

i'm reading it but not sure how to do it.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-06 : 05:57:09

Basically you will need to use either exec() or sp_executesql. The entire query has to be generated dynamically.

Just take a look at your query. The line in red will depends on the number of names you have. Construct the query with the distinct names that you have in that table. Or get it from the name master if there is one.


SELECT call, 
       SUM(CASE WHEN [NAME] = 'Michael' THEN [Score] ELSE 0 END) AS [Michael]
FROM   dbo.JP
GROUP BY call

KH
[spoiler]Time is always against us[/spoiler]

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 2008-08-06 : 07:14:06

not sure how :( can you help?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-06 : 07:33:17

Did you read through the link at all ? I posted the link and 3 mins and 25 secs later you are asking "can you tell me how?". It only takes you 3+ mins to read through that ?

There are lots of example and information on Dynamic SQL in there. Just spend sometime to read and understand it.

I will give you a bit more to start you off with Dynamic SQL.


declare @sql nvarchar(4000)
select @sql = 'SELECT call, 
       SUM(CASE WHEN [NAME] = ''Michael'' THEN [Score] ELSE 0 END) AS [Michael]
FROM   dbo.JP
GROUP BY call'

exec sp_executesql @sql

KH
[spoiler]Time is always against us[/spoiler]

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 2008-08-06 : 07:34:17

how can i use exec() or sp_executesql?

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-08-06 : 07:53:22

cutiebo2t meet Funketun, Funketun meet cutiebo2t

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 2008-08-06 : 08:40:17

yes?

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 2008-08-06 : 08:42:35

When I tried this, I got an error 1 row affected by last query.

declare @sql nvarchar(4000)
select @sql = 'SELECT call,
SUM(CASE WHEN [NAME] = ''Michael'' THEN [Score] ELSE 0 END) AS [Michael]
FROM dbo.JP
GROUP BY call'

exec sp_executesql @sql

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources