Returning rows as columns

Hi,
I have two tables ItemMaster and ItemCondition. One item can have multiple
conditions in the form of rows in ItemCondition. But while returning these,
I
would like to return it in the form of columns. Is that possible?
Below is the DDL of my table structure
CREATE TABLE [dbo].[ItemCondition] (
[ItemConditionId] [int] NOT NULL ,
[ItemId] [int] NOT NULL ,
[ItemCondition] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ItemConditionDesc] [varchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[ItemMaster] (
[ItemId] [int] NOT NULL ,
[ItemName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ItemCondition] WITH NOCHECK ADD
CONSTRAINT [PK_ItemCondition] PRIMARY KEY CLUSTERED
(
[ItemConditionId]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ItemMaster] WITH NOCHECK ADD
CONSTRAINT [PK_ItemMaster] PRIMARY KEY CLUSTERED
(
[ItemId]
) ON [PRIMARY]
GO
Below are some sample inserts
INSERT INTO itemmaster (ItemId, ItemName)
VALUES (1,"CD ROM")
GO
INSERT INTO itemmaster (ItemId, ItemName)
VALUES (2,"Pen drive")
GO
INSERT INTO itemmaster (ItemId, ItemName)
VALUES (3,"DVD")
INSERT INTO ItemCondition (ItemConditionId, ItemId, ItemCondition,
ItemConditionDesc)
VALUES (1,1,"Write once read many","")
GO
INSERT INTO ItemCondition (ItemConditionId, ItemId, ItemCondition,
ItemConditionDesc)
VALUES (2,1,"Silver coated","")
GO
INSERT INTO ItemCondition (ItemConditionId, ItemId, ItemCondition,
ItemConditionDesc)
VALUES (3,1,"700 MB","")
GO
INSERT INTO ItemCondition (ItemConditionId, ItemId, ItemCondition,
ItemConditionDesc)
VALUES (4,3,"Power DVD","")
GO
INSERT INTO ItemCondition (ItemConditionId, ItemId, ItemCondition,
ItemConditionDesc)
VALUES (5,3,"8.2 GB","")
GO
INSERT INTO ItemCondition (ItemConditionId, ItemId, ItemCondition,
ItemConditionDesc)
VALUES (6,2,"1 GB","")
GO
INSERT INTO ItemCondition (ItemConditionId, ItemId, ItemCondition,
ItemConditionDesc)
VALUES (7,2,"Write lock","")
GO
INSERT INTO ItemCondition (ItemConditionId, ItemId, ItemCondition,
ItemConditionDesc)
VALUES (8,2,"password protection","")
GO
Sincerely appreciate any help on this.
Thank you.
Regards,
KarthikHave a look at
http://www.aspfaq.com/show.asp?id=2462
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Karthik" <Karthik@.discussions.microsoft.com> wrote in message
news:F279A4E0-72C3-466D-A122-66F885679485@.microsoft.com...
> Hi,
> I have two tables ItemMaster and ItemCondition. One item can have multiple
> conditions in the form of rows in ItemCondition. But while returning
> these, I
> would like to return it in the form of columns. Is that possible?
> Below is the DDL of my table structure
> CREATE TABLE [dbo].[ItemCondition] (
> [ItemConditionId] [int] NOT NULL ,
> [ItemId] [int] NOT NULL ,
> [ItemCondition] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ,
> [ItemConditionDesc] [varchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[ItemMaster] (
> [ItemId] [int] NOT NULL ,
> [ItemName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[ItemCondition] WITH NOCHECK ADD
> CONSTRAINT [PK_ItemCondition] PRIMARY KEY CLUSTERED
> (
> [ItemConditionId]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[ItemMaster] WITH NOCHECK ADD
> CONSTRAINT [PK_ItemMaster] PRIMARY KEY CLUSTERED
> (
> [ItemId]
> ) ON [PRIMARY]
> GO
>
> Below are some sample inserts
> INSERT INTO itemmaster (ItemId, ItemName)
> VALUES (1,"CD ROM")
> GO
> INSERT INTO itemmaster (ItemId, ItemName)
> VALUES (2,"Pen drive")
> GO
> INSERT INTO itemmaster (ItemId, ItemName)
> VALUES (3,"DVD")
>
> INSERT INTO ItemCondition (ItemConditionId, ItemId, ItemCondition,
> ItemConditionDesc)
> VALUES (1,1,"Write once read many","")
> GO
> INSERT INTO ItemCondition (ItemConditionId, ItemId, ItemCondition,
> ItemConditionDesc)
> VALUES (2,1,"Silver coated","")
> GO
> INSERT INTO ItemCondition (ItemConditionId, ItemId, ItemCondition,
> ItemConditionDesc)
> VALUES (3,1,"700 MB","")
> GO
> INSERT INTO ItemCondition (ItemConditionId, ItemId, ItemCondition,
> ItemConditionDesc)
> VALUES (4,3,"Power DVD","")
> GO
> INSERT INTO ItemCondition (ItemConditionId, ItemId, ItemCondition,
> ItemConditionDesc)
> VALUES (5,3,"8.2 GB","")
> GO
> INSERT INTO ItemCondition (ItemConditionId, ItemId, ItemCondition,
> ItemConditionDesc)
> VALUES (6,2,"1 GB","")
> GO
> INSERT INTO ItemCondition (ItemConditionId, ItemId, ItemCondition,
> ItemConditionDesc)
> VALUES (7,2,"Write lock","")
> GO
> INSERT INTO ItemCondition (ItemConditionId, ItemId, ItemCondition,
> ItemConditionDesc)
> VALUES (8,2,"password protection","")
> GO
> Sincerely appreciate any help on this.
> Thank you.
> Regards,
> Karthik|||Hi
Usually you are better doing this on the client.
If there are a maximum number of possibilities then you can use self-joins
such as :
SELECT M.ItemId, M.ItemName, C1.ItemCondition, C2.ItemCondition,
C3.ItemCondition, ....
FROM ItemMaster I
LEFT JOIN ItemCondition C1 ON I.ItemId = C1.ItemId AND C1.ItemConditionId =
1
LEFT JOIN ItemCondition C2 ON I.ItemId = C2.ItemId AND C2.ItemConditionId =
2
LEFT JOIN ItemCondition C3 ON I.ItemId = C3.ItemId AND C3.ItemConditionId =
3
LEFT JOIN ItemCondition C4 ON I.ItemId = C4.ItemId AND C4.ItemConditionId =
4
LEFT JOIN ItemCondition C5 ON I.ItemId = C5.ItemId AND C5.ItemConditionId =
5
You may also want to look at:
http://www.winnetmag.com/SQLServer/...5608/15608.html
and other postings on CROSSTABs.
John
"Karthik" wrote:

> Hi,
> I have two tables ItemMaster and ItemCondition. One item can have multiple
> conditions in the form of rows in ItemCondition. But while returning these
, I
> would like to return it in the form of columns. Is that possible?
> Below is the DDL of my table structure
> CREATE TABLE [dbo].[ItemCondition] (
> [ItemConditionId] [int] NOT NULL ,
> [ItemId] [int] NOT NULL ,
> [ItemCondition] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ItemConditionDesc] [varchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[ItemMaster] (
> [ItemId] [int] NOT NULL ,
> [ItemName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[ItemCondition] WITH NOCHECK ADD
> CONSTRAINT [PK_ItemCondition] PRIMARY KEY CLUSTERED
> (
> [ItemConditionId]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[ItemMaster] WITH NOCHECK ADD
> CONSTRAINT [PK_ItemMaster] PRIMARY KEY CLUSTERED
> (
> [ItemId]
> ) ON [PRIMARY]
> GO
>
> Below are some sample inserts
> INSERT INTO itemmaster (ItemId, ItemName)
> VALUES (1,"CD ROM")
> GO
> INSERT INTO itemmaster (ItemId, ItemName)
> VALUES (2,"Pen drive")
> GO
> INSERT INTO itemmaster (ItemId, ItemName)
> VALUES (3,"DVD")
>
> INSERT INTO ItemCondition (ItemConditionId, ItemId, ItemCondition,
> ItemConditionDesc)
> VALUES (1,1,"Write once read many","")
> GO
> INSERT INTO ItemCondition (ItemConditionId, ItemId, ItemCondition,
> ItemConditionDesc)
> VALUES (2,1,"Silver coated","")
> GO
> INSERT INTO ItemCondition (ItemConditionId, ItemId, ItemCondition,
> ItemConditionDesc)
> VALUES (3,1,"700 MB","")
> GO
> INSERT INTO ItemCondition (ItemConditionId, ItemId, ItemCondition,
> ItemConditionDesc)
> VALUES (4,3,"Power DVD","")
> GO
> INSERT INTO ItemCondition (ItemConditionId, ItemId, ItemCondition,
> ItemConditionDesc)
> VALUES (5,3,"8.2 GB","")
> GO
> INSERT INTO ItemCondition (ItemConditionId, ItemId, ItemCondition,
> ItemConditionDesc)
> VALUES (6,2,"1 GB","")
> GO
> INSERT INTO ItemCondition (ItemConditionId, ItemId, ItemCondition,
> ItemConditionDesc)
> VALUES (7,2,"Write lock","")
> GO
> INSERT INTO ItemCondition (ItemConditionId, ItemId, ItemCondition,
> ItemConditionDesc)
> VALUES (8,2,"password protection","")
> GO
> Sincerely appreciate any help on this.
> Thank you.
> Regards,
> Karthik