February 17, 2004 at 7:31 am
CREATE TABLE [dbo].[PART_SECTION] (
[sPostID] [varchar] (10) COLLATE Latin1_General_CI_AS NOT NULL ,
[iPartID] [smallint] NOT NULL ,
[sSectionType] [varchar] (1) COLLATE Latin1_General_CI_AS NOT NULL ,
[sSection] [varchar] (20) COLLATE Latin1_General_CI_AS NULL ,
[CreateDate] [datetime] NULL ,
[Changes] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[User_ID] [int] NULL
) ON [PRIMARY]
GO
INSERT INTO Part_section VALUES('E1.1', 1, 'S', 'SS01', '2004.02.17 12:00', 'Update', 32)
INSERT INTO Part_section VALUES('E1.1', 1, 'A', 'AA01', '2004.02.17 12:00', 'Update', 32)
INSERT INTO Part_section VALUES('E1.1', 1, 'M', 'MM01', '2004.02.17 12:00', 'Update', 32)
INSERT INTO Part_section VALUES('E1.1', 1, 'F', 'FF01', '2004.02.17 12:00', 'Update', 32)
INSERT INTO Part_section VALUES('E1.1', 1, 'D', 'DD01', '2004.02.17 12:00', 'Update', 32)
INSERT INTO Part_section VALUES('E1.1', 1, 'P', 'PP01', '2004.02.17 12:00', 'Update', 32)
INSERT INTO Part_section VALUES('E1.1', 1, 'C', 'CC01', '2004.02.17 12:00', 'Update', 32)
INSERT INTO Part_section VALUES('E1.1', 1, 'I', 'HH01', '2004.02.17 12:00', 'Update', 32)
select * from Part_Section where sPostID = 'E1.1' and iPartID = 1
OK! So far so good. Now you have eight rows. I would like a query to have only one row. How do I do that?
The resulting rows should look like this:
sPostID, iPartID, SS01, AA01, MM01, FF01, DD01, PP01, CC01, HH01. Based on E1.1
-Lars
Please only reply to this newsgroup. All mails would be bounced back.
February 17, 2004 at 10:04 am
This is a job that should be done at FRONT-END
but:
just as an example ...
CREATE FUNCTION dbo.udf_AllSections(@sPostID varchar(10), @iPartID int)
RETURNS varchar(8000)
BEGIN
DECLARE @Sections varchar(8000)
SELECT @Sections = ISNULL(@Sections + ',',@sPostID + ',' + cast(@iPartID as varchar(10))+ ',' ) + sSection
FROM Part_Section
WHERE sPostID = @sPostID and iPartID = @iPartID
RETURN @Sections
END
SELECT dbo.udf_AllSections('E1.1', 1)
* Noel
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply