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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2007-02-14 : 08:36:37
|
| Osama writes "I have a table which is like :Emp_No | G_No | A_No | I_No | Note | Amount------- | ------| ------ | ------| ----------| ------ 1 | 1 | 2 | 3 | abc | 100 1 | 3 | 4 | 2 | cde | 180 1 | 5 | 1 | 2 | reg | 50 2 | 1 | 2 | 3 | abc | 120 2 | 3 | 4 | 2 | cde | 75 . | . | . | . | . | . . | . | . | . | . | . . | . | . | . | . | . . | . | . | . | . | .and i need this data to be presented like :Emp_No | abc | cde | reg | . . .------- | ----- | -----| ----- | 1 | 100 | 180 | 50 | . . . 2 | 120 | 75 | 0 | . . . . | . | . | . | . . . . | . | . | . | . | . | . | . |the idea is : i need the query to group by the emp_no at the first level, every column has been represented from three attributes which are G_No,A_No and I_No , the column header is the note column beside and the amount which i need would be represented as previous." |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2007-02-14 : 09:05:35
|
[code]select Emp_No, [abc] = max(case when Note = 'abc' then Amount end), [cde] = max(case when Note = 'cde' then Amount end), [reg] = max(case when Note = 'reg' then Amount end) . . . from table1group by Emp_No[/code]
KH |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-02-14 : 09:06:55
|
| Are there any more Notes than 'abc', 'cde' and 'reg'?Peter LarssonHelsingborg, Sweden |
|
|
|
Osamadweik
Starting Member
1 Post |
Posted - 2007-02-14 : 10:19:57
|
| You note that 'abc', 'cde' and 'reg' are data which i can't expect at the end, how can generalize it so i my not care what data is entered. |
|
|
|
|
|
|
Pivot crosstab query