April 8, 2002 at 10:27 am
data set
id name codedescription date number
1 'abc' 'is to be' 1/1/1900 7
1 'xyz' 'is to be' 1/1/1900 7
output
id name codedescription date number
1 'abc& xyz' 'is to be' 1/1/1900 7
i am trying the group by but i cannot get the names in this format. any suggestions.
April 8, 2002 at 11:06 am
You will not be able to group by as the data varies, a piivot table won't work here and you cannot join to self enough to cover every possiblity.
However this may do the trick without a cursor, but it may also take a bit of playing with to find out what works best. Otherwise I don't see to many other ways without a cursor.
CREATE TABLE #tempTbl (
[recid] [int] IDENTITY (1,1) NOT NULL,
[id] [int] NOT NULL,
[name] [varchar] (1000) NOT NULL,
[codedescription] [varchar] (50) NOT NULL,
[date] [smalldatetime] NOT NULL,
[number] [int] NOT NULL
)
INSERT INTO #tempTbl ([id], [name], codedescription, [date], number)
SELECT DISTINCT [id], '', codedescription, [date], number FROM myMainTbl
DECLARE @loops AS INT
DECLARE @loopon AS INT
SET @loops = (SELECT COUNT(*) FROM #tempTbl)
SET @loopon = 1
DECLARE @namedata AS VARCHAR(1000)
WHILE @loopon <= @loops
BEGIN
SET @namedata = ''
SELECT @namedata = @namedata + ' & ' + mMT.[name] FROM myMainTbl mMT INNER JOIN
#tempTbl tT ON
mMT.[id] = tT.[id] AND
mMT.codedescription = tT.codedescription AND
mMT.[date] = tT.[date] AND
mMT.number = tT.number
WHERE tT.recid = @loopon
SET @namedata = RIGHT(@namedata, LEN(@namedata) - 3)
UPDATE #tempTbl SET [name] = @namedata
SET @loopon = @loopon + 1
END
SELECT * FROM #tempTbl
DROP TABLE #tempTbl
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
April 8, 2002 at 3:30 pm
Another possibility:
DECLARE @ID INT,
@Name varchar(8000)
set @ID =0
set nocount on
WHILE@ID IS NOT NULL
BEGIN
SELECT @ID =min(ID)
FROMmyTable
WHEREID > @ID
IF@ID IS NOT NULL
BEGIN
select @Name = @Name + ' & ' + rtrim(name)
FROMmyTable
where ID= @ID
END
select distinct ID, substring(@Name,4,len(@Name))as 'name', codedescription, date, number
FROMmyTable
where ID= @ID
set @Name =''
END
April 9, 2002 at 5:59 pm
CREATE TABLE #tempTbl (
[recid] [int] IDENTITY (1,1) NOT NULL,
[id] [int] NOT NULL,
[name] [varchar] (1000) NOT NULL,
[codedescription] [varchar] (50) NOT NULL,
[date] [smalldatetime] NOT NULL,
[number] [int] NOT NULL
)
INSERT INTO #tempTbl ([id], [name], codedescription, [date], number)
SELECT DISTINCT [id], '', codedescription, [date], number FROM myMainTbl
DECLARE @loops AS INT
DECLARE @loopon AS INT
SET @loops = (SELECT COUNT(*) FROM #tempTbl)
SET @loopon = 1
DECLARE @namedata AS VARCHAR(1000)
<b>DECLARE @id NUMERIC</b>
WHILE @loopon <= @loops
BEGIN
SET @namedata = ''
<b>SET @id = 0 </b>
SELECT @namedata = @namedata + ' & ' + mMT.[name], <b>@id = tT.id</b> FROM myMainTbl mMT INNER JOIN
#tempTbl tT ON
mMT.[id] = tT.[id] AND
mMT.codedescription = tT.codedescription AND
mMT.[date] = tT.[date] AND
mMT.number = tT.number
WHERE tT.recid = @loopon
SET @namedata = RIGHT(@namedata, LEN(@namedata) - 3)
UPDATE #tempTbl SET [name] = @namedata
<b>WHERE tT.id = @id</b>
SET @loopon = @loopon + 1
END
SELECT * FROM #tempTbl
DROP TABLE #tempTbl
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply