sql server programming: Returning rows as columns -- sql server programming

Returning rows as columns

Karthik
10/25/2005 11:29:03 PM

sql server programming:

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,

RE: Returning rows as columns

John Bell
10/25/2005 11:55:02 PM

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/Article/ArticleID/15608/15608.html

and other postings on CROSSTABs.

John

[quoted text, click to view]

Re: Returning rows as columns

Roji. P. Thomas
10/26/2005 12:18:43 PM

Have a look at

http://www.aspfaq.com/show.asp?id=2462

--
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server programming group.