Cross Tab SELECT

I am using MS-SQL2000, on a table called tblItems with these columns:
ID Descripction
-- --
0012 Washer
0145 Oven
2345 Heater
7834 Fan
and other called tblFeatures
ID Description
-- --
0012 White
0012 Large
0012 Electrical
0145 Brown
0145 Small
0145 Hybrid
I want to make a query with next result
ID Item F1 F2
F3
-- -- -- --
--
0012 Washer White Large
Electrical
0145 Oven Brown Small Hybri
d
.
.
.
How can I do this? Please, help me.
Beforehand, thank you very much.
Luis Garcia
IT ConsultantLuis, try:
SELECT ID, Item
, MAX(CASE WHEN pos = 1 THEN Feature END) AS F1
, MAX(CASE WHEN pos = 2 THEN Feature END) AS F2
, MAX(CASE WHEN pos = 3 THEN Feature END) AS F3
/* add more items here */
FROM (SELECT F.ID, I.Description AS Item, F.Description AS Feature,
(SELECT COUNT(*)
FROM tblFeatures AS F2
WHERE F2.ID = F.ID
AND F2.Description <= F.Description) AS Pos
FROM tblItems AS I
JOIN tblFeatures AS F
ON F.ID = I.ID) AS D
GROUP BY ID, Item;
BG, SQL Server MVP
www.SolidQualityLearning.com
www.insidetsql.com
Anything written in this message represents my view, my own view, and
nothing but my view (WITH SCHEMABINDING), so help me my T-SQL code.
"LUIS" <LUIS@.discussions.microsoft.com> wrote in message
news:6D153B9E-5373-49B3-8E27-AD6B69F27B35@.microsoft.com...
>I am using MS-SQL2000, on a table called tblItems with these columns:
> ID Descripction
> -- --
> 0012 Washer
> 0145 Oven
> 2345 Heater
> 7834 Fan
> and other called tblFeatures
> ID Description
> -- --
> 0012 White
> 0012 Large
> 0012 Electrical
> 0145 Brown
> 0145 Small
> 0145 Hybrid
> I want to make a query with next result
> ID Item F1 F2
> F3
> -- -- -- --
> --
> 0012 Washer White Large
> Electrical
> 0145 Oven Brown Small
> Hybrid
> .
> .
> .
> How can I do this? Please, help me.
> Beforehand, thank you very much.
>
> --
> Luis Garcia
> IT Consultant|||Itzik Ben-Gan
Thank you very much. Your answer was very useful to me.
Luis Garcia
IT Consultant
"Itzik Ben-Gan" wrote:

> Luis, try:
> SELECT ID, Item
> , MAX(CASE WHEN pos = 1 THEN Feature END) AS F1
> , MAX(CASE WHEN pos = 2 THEN Feature END) AS F2
> , MAX(CASE WHEN pos = 3 THEN Feature END) AS F3
> /* add more items here */
> FROM (SELECT F.ID, I.Description AS Item, F.Description AS Feature,
> (SELECT COUNT(*)
> FROM tblFeatures AS F2
> WHERE F2.ID = F.ID
> AND F2.Description <= F.Description) AS Pos
> FROM tblItems AS I
> JOIN tblFeatures AS F
> ON F.ID = I.ID) AS D
> GROUP BY ID, Item;
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
> www.insidetsql.com
> Anything written in this message represents my view, my own view, and
> nothing but my view (WITH SCHEMABINDING), so help me my T-SQL code.
>
> "LUIS" <LUIS@.discussions.microsoft.com> wrote in message
> news:6D153B9E-5373-49B3-8E27-AD6B69F27B35@.microsoft.com...
>
>|||Yes you can walk..but if you want to ride check out Rac:
for dynamic crosstabs.(See the @.rank parameter).
www.rac4sql.net|||Dear Itzik:
Here, once again, Luis. Sorry! I was in a hurry when I saw your answer and
implemented it as soon as I can in my application. I have noted that you are
a very experimented developer, and very succeful too. Thank you very much fo
r
your time dedicated to me. I did not finish sorprised by the query you gave
me, it is extraordinary by utility and simplicity. I wonder for some
literature to learn more about, but I already access your links and see your
books. Nothing more to say. I hope to keep in touch with you and increase
knowledge.
Once again, thank you very much
Luis Garcia
IT Consultant
"Itzik Ben-Gan" wrote:

> Luis, try:
> SELECT ID, Item
> , MAX(CASE WHEN pos = 1 THEN Feature END) AS F1
> , MAX(CASE WHEN pos = 2 THEN Feature END) AS F2
> , MAX(CASE WHEN pos = 3 THEN Feature END) AS F3
> /* add more items here */
> FROM (SELECT F.ID, I.Description AS Item, F.Description AS Feature,
> (SELECT COUNT(*)
> FROM tblFeatures AS F2
> WHERE F2.ID = F.ID
> AND F2.Description <= F.Description) AS Pos
> FROM tblItems AS I
> JOIN tblFeatures AS F
> ON F.ID = I.ID) AS D
> GROUP BY ID, Item;
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
> www.insidetsql.com
> Anything written in this message represents my view, my own view, and
> nothing but my view (WITH SCHEMABINDING), so help me my T-SQL code.
>
> "LUIS" <LUIS@.discussions.microsoft.com> wrote in message
> news:6D153B9E-5373-49B3-8E27-AD6B69F27B35@.microsoft.com...
>
>|||Luis,
No need to apologize; it's my pleasure. Your original thank you was more
than I could handle.
I'm actually lost for words when the subject matter is not SQL; and as
proof, replying to you took me more time then producing a transitive closure
of a graph with a recursive query. ;-)
Cheers,
--
BG, SQL Server MVP
www.SolidQualityLearning.com
www.insidetsql.com
Anything written in this message represents my view, my own view, and
nothing but my view (WITH SCHEMABINDING), so help me my T-SQL code.
"LUIS" <LUIS@.discussions.microsoft.com> wrote in message
news:CB89285B-CD92-4DA5-B91D-AD668FEC358F@.microsoft.com...
> Dear Itzik:
> Here, once again, Luis. Sorry! I was in a hurry when I saw your answer and
> implemented it as soon as I can in my application. I have noted that you
> are
> a very experimented developer, and very succeful too. Thank you very much
> for
> your time dedicated to me. I did not finish sorprised by the query you
> gave
> me, it is extraordinary by utility and simplicity. I wonder for some
> literature to learn more about, but I already access your links and see
> your
> books. Nothing more to say. I hope to keep in touch with you and increase
> knowledge.
> Once again, thank you very much
> --
> Luis Garcia
> IT Consultant
>
> "Itzik Ben-Gan" wrote:
>