sql question - 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

sql question

Author

Topic

kongaung
Starting Member

8 Posts

Posted - 2009-01-09 : 00:42:49

i would like to get the result as follow

select NAME from employee

NAME
Davolio
Fuller
Leverling
Peacock
Buchanan
Suyama
King
Callahan
Dodsworth

THIS RESULT IS FROM QUERY

I WOULD LIKE TO CHANGE ALL ROWS AS COLUMNS

SUCH AS to get this result

Davolio Fuller Leverling Peacock Buchanan Suyama King Callahan Dodsworth

I write my query as follow

select col_name(object_ID('employees') ,2) on columns,
NON EMPTY {[Mydatatable].[name ].Members} ON ROWS
from Mydatatable,employees

but error

how can i write my query.

best regard

kongaung
myanmar

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-09 : 00:48:21

use pivot function

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-01-09 : 00:55:55

uSE pIVOT iF U R wORKING iN sQL 2005,,,,,,,,,,,,
SEE THIS U MAY GET AN IDEA HOW TO DO THIS......

http://msdn.microsoft.com/en-us/library/ms177410(SQL.90).aspx

tHANKS....

jbp_j
Starting Member

24 Posts

Posted - 2009-01-09 : 01:18:33

hi,

try this one.

declare @var varchar(max)
set @var = ''
select @var = stuff(( select ','+name from yourtable for xml path('')),1,1,'')
select @var
select @var =''''+ replace(@var,',',''' as name ,''')+''''

exec('select'+@var)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-09 : 01:45:20

quote:
Originally posted by jbp_j

hi,

try this one.

declare @var varchar(max)
set @var = ''
select @var = stuff(( select ','+name from yourtable for xml path('')),1,1,'')
select @var
select @var =''''+ replace(@var,',',''' as name ,''')+''''

exec('select'+@var)

why use dynamic sql? cant you just execute select as it is without using exec
also this will just provide a single comma seperated list of values which is not what OP wants. he wants value in separate columns

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-01-09 : 02:58:30

Search for Dynamic Cross tab in google

Madhivanan

Failing to plan is Planning to fail

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2009-01-09 : 03:36:57

SELECT 'Davolio' as col1, 'Fuller'AS col2,'Leverling' AS col3,'Peacock' AS col4
FROM (select * from tab1) AS P
PIVOT(MAX(Name) For Name IN([Davolio],[Fuller],[Leverling],[Peacock])) AS PVT

Jai Krishna

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-01-09 : 03:36:58

declare @name table (NAME varchar(32))
insert into @name
select 'Davolio' union all
select 'Fuller' union all
select 'Leverling' union all
select 'Peacock' union all
select 'Buchanan' union all
select 'Suyama' union all
select 'King' union all
select 'Callahan' union all
select 'Dodsworth'

select
max(case when name = 'Davolio'then name else null end) Davolio,
max(case when name = 'Fuller'then name else null end) Fuller,
max(case when name ='Leverling'then name else null end) Leverling,
max(case when name ='Peacock'then name else null end) Peacock,
max(case when name ='Buchanan'then name else null end) Buchanan,
max(case when name ='Suyama'then name else null end) Suyama,
max(case when name ='King'then name else null end) King,
max(case when name ='Callahan'then name else null end) Callahan,
max(case when name ='Dodsworth'then name else null end) as Dodsworth from @name

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2009-01-09 : 03:37:17

SELECT
MAX(CASE Name WHEN 'Davolio' THEN Name ELSE NULL END),
MAX(CASE Name WHEN 'Fuller' THEN Name ELSE NULL END),
MAX(CASE Name WHEN 'Leverling' THEN Name ELSE NULL END),
MAX(CASE Name WHEN 'Peacock' THEN Name ELSE NULL END)
FROM tab1

Jai Krishna

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-09 : 04:52:28

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/dynamic-crosstab-with-multiple-pivot-columns.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources