Regarding a query

Hello,
I am facing a very complex problem in front of me. I dont know if its complex...Kindly help me out in acheiving the same.

Say I have a table called InfoName with two columns Name and ID

InfoName

Name ID

OS 1
SP 2
Driver 3
fasdf **
** ***
** ****

(I AM INTERESTED IN ONLY FIRST THREE ROWS )

I have another table Infotxt which uses the ID of InfoName as foreign key. It stores the value of this ID as shown

InFotxt

ID Value UnitNAME

1 Win 2000 raj
2 SP 4 raj
3 40 GB raj

1 Win xp jay
2 SP 2 jay
3 20 GB jay

NOw I need to present it with unitname's configuration of OS, Sp and disk capacity like below.

name OS SP Drive
Raj win2000 sp4 40 GB
Jay winxp sp2 2o GB

That is, the rows of the InfoName table (first 3 rows) should be the columns of my resultant query.

How can I achieve the same.
Please give me some ideas, and if the question is silly, I am very sorry, because I am new to database queries...

Thanks,
cspekWhat you are trying to achieve is called a cross-tab query (or a pivot table). I think you should be able to do something like this:

SELECT
 I.UnitName,
 CASE WHEN I.ID = 1 THEN MAX(I.Value) ELSE NULL END AS OS,
 CASE WHEN I.ID = 2 THEN MAX(I.Value) ELSE NULL END AS SP,
 CASE WHEN I.ID = 3 THEN MAX(I.Value) ELSE NULL END AS Drive
FROM
 InFotxt I
GROUP BY
 I.UnitName
ORDER BY
 I.UnitName

Terri|||I got the following error with the above select query

Server: Msg 8120, Level 16, State 1, Line 1
Column 'I.ID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

You can try the below statement,

select
unitName,
[Value] = (SELECT [Value] from InFotxt Where ID = 1 and s.unitName = unitName),
[Value] = (SELECT [Value] from InFotxt Where ID = 2 and s.unitName = unitName),
[Value] = (SELECT [Value] from InFotxt Where ID = 3 and s.unitName = unitName)
from (
select
UnitName
from InFotxt
group by UnitName
) S|||You must agggregate any columns not contained in the group by, ie:

MAX(CASE WHEN I.ID = 1 THEN MAX(I.Value) ELSE NULL END) as col1

sql