column name and values - 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

SQL Server 2005 Forums

Transact-SQL (2005)

column name and values

Author

Topic

kiri
Starting Member

38 Posts

Posted - 2008-02-07 : 14:17:55

i have one table - metrix
colmns are
indx,
num,
task,
taskdesc

in task column - (values)
proj1,
proj2

taskdesc - (values)
sproc1(if proj1 then values-sproc1)
func1(if proj1 then values-func1)

sproc2(if proj2 then values-sproc2)
func2(if proj2 then values-func2)
view2(if proj2 then values-view2)

now i have to use this table name and columnname using select in query..

select proj1
case when task = proj1 and indx =1 then taskdesc else null

end ,
proj2 = case
when task = proj2 and indx = 1 then taskdesc
when task = proj2 and indx = 0 then taskdesc

end

FROM metrix
where task = proj1 or task = proj2

but it gives wrong results as i want proj2=sproc2,func2,view2 instead of it gives - sproc1,null

proj1 = sproc1,func2..is giving me correct result...so i think instead of taskdesc i have to use proj1.taskdesc but it gives me error..invalid colname proj1...

anyone have solution..
thanx

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-08 : 00:29:05

Your requirement is not fuly clear. But i think this is what you are looking at:-

SELECT m.task + '=' +LEFT(tdl.taskdesclist,LEN(tdl.taskdesclist)-1)
FROM metrix m
CROSS APPLY (SELECT taskdesc + ',' AS [text()]
             FROM metrix
             WHERE task=m.task
             FOR XML PATH(''))tdl(taskdesclist)

kiri
Starting Member

38 Posts

Posted - 2008-02-08 : 09:06:10

Thanks Visakh16.

i m getting results correct now...i don't know about xml path..and cross apply..but now i got it..thanks for you help..

thanks a lot!!

kiri
Starting Member

38 Posts

Posted - 2008-02-08 : 11:05:28

now i have to do one query using this same select statement:

SELECT distinct m.task ,LEFT(tdl.taskdesclist,LEN(tdl.taskdesclist)-1) as taskdesc
FROM metrix m
CROSS APPLY (SELECT taskdesc + ',' AS [text()]
FROM metrix
WHERE task=m.task
FOR XML PATH(''))tdl(taskdesclist)

i m getting results like:
task taskdesc
------------------------------------------
proj1 sproc1,func1,sproc1,func1,sproc1,func1
proj2 sproc2,func2,view2,sproc2,func2,view2,sproc2,func2,view2

results r correct...only 2 rows i want so i use distinct...but i don't want repetetive values of taskdesc....means in proj1 - sproc1,func1 are 5times then i m g etting it 5times..i want only one time...and like these:

proj1 proj2
-------------------------------------------
sproc1,func1 sproc2,func2,view2

can u tell me how can i get it results this way.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-08 : 12:30:24

quote:
Originally posted by kiri

now i have to do one query using this same select statement:

SELECT distinct m.task ,LEFT(tdl.taskdesclist,LEN(tdl.taskdesclist)-1) as taskdesc
FROM metrix m
CROSS APPLY (SELECT taskdesc + ',' AS [text()]
FROM metrix
WHERE task=m.task
FOR XML PATH(''))tdl(taskdesclist)

i m getting results like:
task taskdesc
------------------------------------------
proj1 sproc1,func1,sproc1,func1,sproc1,func1
proj2 sproc2,func2,view2,sproc2,func2,view2,sproc2,func2,view2

results r correct...only 2 rows i want so i use distinct...but i don't want repetetive values of taskdesc....means in proj1 - sproc1,func1 are 5times then i m g etting it 5times..i want only one time...and like these:

proj1 proj2
-------------------------------------------
sproc1,func1 sproc2,func2,view2

can u tell me how can i get it results this way.

try like this

SELECT p.proj1,p.proj2
FROM
(
SELECT distinct m.task ,LEFT(tdl.taskdesclist,LEN(tdl.taskdesclist)-1) as taskdesc
FROM metrix m
CROSS APPLY (SELECT distinct taskdesc + ',' AS [text()]
FROM metrix
WHERE task=m.task
FOR XML PATH(''))tdl(taskdesclist)
)d
PIVOT
(MAX(taskdesc) FOR
task in ([proj1],[proj2]))p

kiri
Starting Member

38 Posts

Posted - 2008-02-08 : 13:01:30

it gives me error:

i tried p instead of d...
but still same error getting

Incorrect syntax near 'pivot'. You may need to set the compatibility level of the current database to a higher value to enable this feature. See help for the stored procedure sp_dbcmptlevel.

thanks for ur reply.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-08 : 13:13:14

Can you check if your db compatibilty level is set to 90?

kiri
Starting Member

38 Posts

Posted - 2008-02-08 : 13:28:06

it was 80 and i alter database and set it compatibility level to 90

ya, now i am not getting error..thanks you so much.
thanks a lot.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-08 : 13:32:29

quote:
Originally posted by kiri

it was 80 and i alter database and set it compatibility level to 90

ya, now i am not getting error..thanks you so much.
thanks a lot.

cool..cheers

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources