Alias a column that has 3 values into 3 columns

I want to alias a column that has 3 values into 3 columns, one for each
value. As an example, let’s say Pet_type.CategoryID contains three possib
le
values: Cat, Dog and Hamster. The following SQL works with MySQL. What is
the correct SQL Server syntax for this? I looked in the Transact SQL Guide
but came up empty.
Thank you.
Alan Slutsky
SELECT DISTINCT c1.Name AS Dog, c2.Name AS Cat, c3.Name AS Hamster,
d.title, d.identifier
FROM Canonical c1
LEFT JOIN Entity e1 USING (CanonicalID)
LEFT JOIN Document d USING(DocumentID)
LEFT JOIN Entity e2 USING (DocumentID)
LEFT JOIN Canonical c2 USING (CanonicalID)
LEFT JOIN Canonical c3 USING (CanonicalID)
WHERE c1.CategoryID=”Dog” AND c2.CategoryID=”Cat” and c3.CategoryID=
”Hamster”"aslutsky" <aslutsky@.discussions.microsoft.com> wrote in message
news:BE0AEB86-B15F-4E95-A13B-FBD7FFD748B4@.microsoft.com...
>I want to alias a column that has 3 values into 3 columns, one for each
> value. As an example, let's say Pet_type.CategoryID contains three
> possible
> values: Cat, Dog and Hamster. The following SQL works with MySQL. What
> is
> the correct SQL Server syntax for this? I looked in the Transact SQL
> Guide
> but came up empty.
> Thank you.
> Alan Slutsky
> SELECT DISTINCT c1.Name AS Dog, c2.Name AS Cat, c3.Name AS Hamster,
> d.title, d.identifier
> FROM Canonical c1
> LEFT JOIN Entity e1 USING (CanonicalID)
> LEFT JOIN Document d USING(DocumentID)
> LEFT JOIN Entity e2 USING (DocumentID)
> LEFT JOIN Canonical c2 USING (CanonicalID)
> LEFT JOIN Canonical c3 USING (CanonicalID)
> WHERE c1.CategoryID="Dog" AND c2.CategoryID="Cat" and c3.CategoryID="Hamst
er"
>
Here's my best guess. Notice that I've removed the references to C2 and C3
from the WHERE clause, otherwise the outer join effectively becomes and
inner one - at least it does in standard SQL but I can't verify that for
MySQL.
SELECT DISTINCT c1.name AS dog, c2.name AS cat, c3.name AS hamster,
d.title, d.identifier
FROM Canonical c1
LEFT JOIN Entity e1
ON c1.canonicalid = e1.canonicalid
LEFT JOIN Document d
ON c1.canonicalid = d.canonicalid
LEFT JOIN Entity e2
ON c1.canonicalid = e2.canonicalid
LEFT JOIN Canonical c2
ON c1.canonicalid = c2.canonicalid
AND c2.CategoryID='Cat'
LEFT JOIN Canonical c3
ON c1.canonicalid = c3.canonicalid
AND c3.CategoryID='Hamster'
WHERE c1.CategoryID='Dog' ;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||>> I want to alias a column that has 3 values into 3 columns, one for each v
alue. <<
That makes ABSOLUTELY NO SENSE in RDBMS. A column has a single value
by definition?
No such thing can exist! Do you really only have one PetType as
implied by the singlular name? What kidn of crap is catergory_id? An
attribute can be a category OF SOME KIND or an identifier of SOME
ENTITY, but it cannot be both. This is foundations, not rocket
science.
Alan, please get a book on data modeling and SQL! Oh, in MySQL current
version or a Standared SQL you might use " pet_type IN ('Cat', 'Dog',
'Hamster')|||David,
I had to modify the query because d.canonicalid does not exist and c can not
join d. When I joined table d to table e, I get rows returned, but the
Company "column" is the only one that is populated. Person and Product
contain only nulls.
Do you have any other suggestions, and thank you for the help.
Alan
"David Portas" wrote:

> "aslutsky" <aslutsky@.discussions.microsoft.com> wrote in message
> news:BE0AEB86-B15F-4E95-A13B-FBD7FFD748B4@.microsoft.com...
> Here's my best guess. Notice that I've removed the references to C2 and C3
> from the WHERE clause, otherwise the outer join effectively becomes and
> inner one - at least it does in standard SQL but I can't verify that for
> MySQL.
> SELECT DISTINCT c1.name AS dog, c2.name AS cat, c3.name AS hamster,
> d.title, d.identifier
> FROM Canonical c1
> LEFT JOIN Entity e1
> ON c1.canonicalid = e1.canonicalid
> LEFT JOIN Document d
> ON c1.canonicalid = d.canonicalid
> LEFT JOIN Entity e2
> ON c1.canonicalid = e2.canonicalid
> LEFT JOIN Canonical c2
> ON c1.canonicalid = c2.canonicalid
> AND c2.CategoryID='Cat'
> LEFT JOIN Canonical c3
> ON c1.canonicalid = c3.canonicalid
> AND c3.CategoryID='Hamster'
> WHERE c1.CategoryID='Dog' ;
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>
>