Two columns from two rows? - 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)

Two columns from two rows?

Author

Topic

dheritage
Starting Member

3 Posts

Posted - 2011-03-21 : 12:57:24

I've inherited a database where data I would like to display as two columns actually exists in two rows and a related field in another table. (It is a ticketing system).

Table A
TicID, FieldID, IntVal

Table B
FieldID,listOrder,Label

What happens is for every TicID there are three fieldIDs (1,2,3) and thus three IntVals that go along with that. No problem until I want a view that shows a ticid, and the label from Table_B that corresponds to the fieldid/intval for fieldids 2 and 3. I get two rows back, each with the correct data per se, but since the unique 'pair' of data created by combining filedid and intval is what I want.... I cannot see how to get there. Here is the line as I have it so far, but I'd like help on getting this as a single row with two columns and not two rows (if that even makes sense).

select a.ticid, a.fieldid, a.intval, b.label from a left join b on a.intval=b.listorder and a.fieldid=b.fieldid where a.fieldid in (2,3)

X002548
Not Just a Number

15586 Posts

Posted - 2011-03-21 : 13:23:56

how about some sample data and what your expected result should be?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

dheritage
Starting Member

3 Posts

Posted - 2011-03-21 : 16:03:33

Here is some sample data.

Table A
TicID, FieldID, IntVal
26861,2,2
26861,3,4
28456,2,7
28456,3,2
31978,2,1
31978,3,3
33903,2,1
33903,3,4
34702,2,1
34702,3,3

Table B
FieldID,listOrder,Label
2,1,open
2,2,on hold
2,3,closed
2,5,scheduled
2,4,to schedule
2,5,scheduled
2,6,call
2,7,waiting on response
2,8,waiting on permission
2,9,waiting on delivery
2,10,pending closed
3,1,critical
3,2,high
3,3asap
3,4,normal
3,5,low

Human readable, ticket # 26861 has status of on hold and priority of normal
I would like to return only one row for each ticket #, with a column for status and another for priority.

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-03-22 : 14:54:31

Check if this is what you are looking for

Select A.TicID,Max(Case when A.FieldID=2 then B.Label end) as [Status],Max(Case when A.FieldID=3 then B.Label end) as [Priority]
from @tableA A
Inner Join @tableB B on A.FieldID=B.FieldID and A.IntVal=B.listOrder
Group by A.TicID

Cheers
MIK

dheritage
Starting Member

3 Posts

Posted - 2011-03-22 : 15:33:26

Fabulous! Just Fabulous! Thank you so very much.

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-03-22 : 15:45:01

you are welcome

Cheers
MIK

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources