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

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 2008-08-06 : 02:51:44
any help?
Go to Top of Page

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

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 2008-08-06 : 03:09:15
it's 2000
Go to Top of Page

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

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

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

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 2008-08-06 : 04:10:13
can anyone help me? thanks
Go to Top of Page

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 2008-08-06 : 05:27:25
anyone?
Go to Top of Page

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]

Go to Top of Page

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 2008-08-06 : 05:38:51
can you tell me how?
Go to Top of Page

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]

Go to Top of Page

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

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]

Go to Top of Page

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 2008-08-06 : 07:14:06
not sure how :( can you help?
Go to Top of Page

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]

Go to Top of Page

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 2008-08-06 : 07:34:17
how can i use exec() or sp_executesql?
Go to Top of Page

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-08-06 : 07:53:22
cutiebo2t meet Funketun, Funketun meet cutiebo2t
Go to Top of Page

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 2008-08-06 : 08:40:17
yes?
Go to Top of Page

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

- Advertisement -