query problem - 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

query problem

Author

Topic

wormz666
Posting Yak Master

110 Posts

Posted - 2008-11-17 : 09:53:39

i want to query a item dynamically definding on its pdesc
how can i name the field in a query,where that the naming of of field is defending on its data in the pdesc

example:
emp_mas
-----------
emp_id
lname
fname
mname

emp_det
------------
emp_id
pos_id

position_desc
-------------
pos_id
pdesc

assuming i this is the data on the table

emp_mas
---------------------------
emp_id lname fname mname
1 f f f
2 r r r
3 s s s
4 d d d
emp_det
---------------------------
emp_id pos_id
1 1
2 2
3 3
4 3
position
----------------------------
pos_id pdesc
1 administrator
2 manager
3 teller

i have my output to be like this

administrator manager teller
1 1 2

please help................

thank you in advance....

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-11-17 : 11:23:09

Didn't understand the output. You mean empid 1 is an administrator and a manager?? And empid 2 is teller ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-17 : 11:28:46

DECLARE @Sql varchar(8000)

SELECT DISTINCT @sql=coalesce(@sql+',','')+ 'COUNT(case when pdesc= ' + pdesc + ' THEN emp_id ELSE NULL END) AS ' + pdesc
FROM position

SET @sql='SELECT '+@Sql + ' FROM position INNER JOIN emp_det ON emp_det.pos_id=position.pos_id'
EXEC(@sql)

wormz666
Posting Yak Master

110 Posts

Posted - 2008-11-18 : 06:33:43

i want to a dynamic naming of field the on a query and placing defending on my pdesc value where the pdesc value count how many employee is on the position.........

wormz666
Posting Yak Master

110 Posts

Posted - 2008-11-18 : 07:21:54

[code]emp_mas
-----------
emp_id
lname
fname
mname

emp_det
------------
emp_id
pos_id

position_desc
-------------
pos_id
pdesc

assuming i this is the data on the table

emp_mas
---------------------------
emp_id lname fname mname
1 f f f
2 r r r
3 s s s
4 d d d
emp_det
---------------------------
emp_id pos_id
1 1
2 2
3 3
4 3
position
----------------------------
pos_id pdesc
1 administrator
2 manager
3 teller

i have my output to be like this

administrator manager teller --column is the value on the pdesc
1 1 2 --while this one count the record that has relation to the column[/code]

if i add a new data on the pdesc then it will have a new column on the query......

i dont know how i will create a query on this

please help............

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-18 : 08:50:06

Did you try my suggestion?

wormz666
Posting Yak Master

110 Posts

Posted - 2008-11-20 : 09:35:26

i get an error of 'invalid column name' on the pdesc............im using mssql 2000

wormz666
Posting Yak Master

110 Posts

Posted - 2008-11-20 : 19:52:19

i get an error of 'invalid column name' on the pdesc............im using mssql 2000