SQL CHAR field question
-
create table #temp
(product_id int not null,
prod_desc char(1) null)
insert into #temp
select 1, 'A'
insert into #temp
select 1, 'B'
insert into #temp
select 1, 'C'
insert into #temp
select 2, 'D'
insert into #temp
select 2, 'E'
insert into #temp
select 2, 'F'
select *
from #temp
product_id prod_desc
----------- ---------
1 A
1 B
1 C
2 D
2 E
2 F
Instead of dispaying as represented above, I need to display this as
product_id prod_desc
----------- ---------
1 A, B, C
2 D, E, F
How can I achieve this?
Thanks.
-
http://sqljunkies.com/WebLog/amachanic/archive/2004/11/10/5065.aspx?Pending=true
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
set nocount on
create table #temp
(product_id int not null,
prod_desc char(1) null)
insert into #temp
select 1, 'A'
insert into #temp
select 1, 'B'
insert into #temp
select 1, 'C'
insert into #temp
select 2, 'D'
insert into #temp
select 2, 'E'
insert into #temp
select 2, 'F'
DECLARE @Output VARCHAR(8000)
SET @Output = ''
SELECT @Output = CASE @Output
WHEN '' THEN prod_desc
ELSE @Output + ', ' + prod_desc
END
FROM #temp
WHERE Product_id = 1
ORDER BY product_id
print @output
--should this out not be A, B, C
--however i received an OUTPUT of just A
-
SELECT @Output = @Output + CASE @Output
WHEN '' THEN prod_desc
ELSE @Output + ', ' + prod_desc
END
FROM #temp
WHERE Product_id = 1
ORDER BY product_id
-
still produced A? missing something....
sql query analyzer version 8.00.194
-
I tried the first script as below - picks only one description - am I missing something?
CREATE FUNCTION dbo.concatdesc(@product_id INT)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @Output VARCHAR(8000)
SET @Output = ''
SELECT @Output = @Output + CASE @Output
WHEN '' THEN prod_desc
ELSE @Output + ', ' + prod_desc
END
FROM prod
WHERE product_id = @product_id
ORDER BY prod_desc
RETURN @Output
END
GO
SELECT DISTINCT product_id, prod_desc = dbo.Concatdesc(product_id)
FROM prod
ORDER BY product_id
product_id prod_desc 1 A 2 D
Thanks
-
I tried the second method suggested - this pulls all the descriptions - however, has it in the reverse order - C,B,A instead of A, B, C - any ideas?
The following is what I tried.
CREATE TABLE #DescConcat
( product_id INT NOT NULL ,
prod_desc VARCHAR(8000) NULL,
Ident INT IDENTITY(1,1) NOT NULL PRIMARY KEY
)
INSERT #DescConcat (product_id, prod_desc)
SELECT product_id, prod_desc
FROM prod
ORDER BY product_id, prod_desc
DECLARE @prod_desc VARCHAR(8000), @product_id INT
SET @prod_desc = ''
SET @product_id = ''
UPDATE #DescConcat
SET @prod_desc = prod_desc =CASE WHEN @product_id = product_id
THEN prod_desc + ', ' + @prod_desc
ELSE prod_desc END,
@product_id = product_id
SELECT product_id, MAX(prod_desc)
FROM #DescConcat
GROUP BY product_id
product_id prod_desc 1 C, B, A
2 F, E, D
Thanks
-
set nocount on
create table #temp
(product_id int not null,
prod_desc char(1) null)
insert into #temp
select 1, 'A'
insert into #temp
select 1, 'B'
insert into #temp
select 1, 'C'
insert into #temp
select 2, 'D'
insert into #temp
select 2, 'E'
insert into #temp
select 2, 'F'
DECLARE @Output VARCHAR(8000)
SET @Output = ''
SELECT @Output = @Output + prod_desc + ', '
FROM #temp
WHERE Product_id = 1
ORDER BY product_id
Select left(@Output, len(@Output) -1) as output
drop table #temp
-
I tried this without the UDF (basically the UDF is the second WHILE is the UDF). It looks long, but if you made the UDF, it should be relatively short...
CREATE TABLE #temp( product_id int NOT NULL,
prod_desc char(1) NULL)
INSERT INTO #temp VALUES( 1, 'A')
INSERT INTO #temp VALUES( 1, 'B')
INSERT INTO #temp VALUES( 1, 'C')
INSERT INTO #temp VALUES( 2, 'D')
INSERT INTO #temp VALUES( 2, 'E')
INSERT INTO #temp VALUES( 2, 'F')
DECLARE @Output varchar(8000),
@ProductDesc varchar(2000),
@ProductID int,
@MaxProductID int,
@Counter int,
@IDCounter int
SET @Output = ''
SELECT @ProductID = (SELECT MIN( product_id) FROM #temp)
SELECT @MaxProductID = (SELECT MAX( product_id) FROM #temp)
SELECT @ProductDesc = (SELECT MIN( prod_desc) FROM #temp
WHERE product_id = @ProductID)
SELECT @Counter = 1
SELECT @IDCounter = ( SELECT COUNT(*) FROM #temp
WHERE product_id = @ProductID)
CREATE TABLE #OutPut( ProductID int,
ProductDesc char(50))
WHILE @ProductID <= @MaxProductID
BEGIN
WHILE @Counter <= @IDCounter
BEGIN
IF LEN( @OutPut) < 1
BEGIN
SELECT @OutPut = @ProductDesc
END
ELSE
BEGIN
SELECT @Output = @OutPut + ', ' + @ProductDesc
END
SELECT @ProductDesc = (SELECT MIN( prod_desc) FROM #temp
WHERE product_id = @ProductID
AND prod_desc > @ProductDesc)
SELECT @Counter = @Counter + 1
END
INSERT INTO #OutPut VALUES( @ProductID, @Output)
SELECT @ProductID = (SELECT MIN( product_id) FROM #temp
WHERE product_id > @ProductID)
SELECT @IDCounter = ( SELECT COUNT(*) FROM #temp
WHERE product_id = @ProductID)
SELECT @ProductDesc = (SELECT MIN( prod_desc) FROM #temp
WHERE product_id = @ProductID)
SELECT @OutPut = ''
SELECT @Counter = 1
SELECT @IDCounter = ( SELECT COUNT(*) FROM #temp
WHERE product_id = @ProductID)
END
SELECT * FROM #OutPut
DROP TABLE #temp
DROP TABLE #OutPut
I wasn't born stupid - I had to study.
-
thanks remi, did not need to use the case
statement. this is a pretty cool tip & trick in the baghttp://sqljunkies.com/WebLog/amachanic/archive/2004/11/10/5065.aspx?Pending=true
hi sheba, still use option 1 as referenced by frank (adam udf clocks good performance), but read remi's POST to make it work (remi removed the case statement - adam's udf might have been tested on a different sql server version but still is a notable tip)
cheers mate
-
I was able to get option 2 to work. However, what Remi suggested brings only one line of output - Farrell's method works without a glitch. How is the efficiency of this script when compared to option 1.
-
My version is meant to be used as a UDF. It's much easier to use and versatile that way.
-
set nocount on
create table #temp
(product_id int not null,
prod_desc char(1) null)
insert into #temp
select 1, 'A'
insert into #temp
select 1, 'B'
insert into #temp
select 1, 'C'
insert into #temp
select 2, 'D'
insert into #temp
select 2, 'E'
insert into #temp
select 2, 'F'
CREATE FUNCTION dbo.concatdesc(@product_id INT)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @Output VARCHAR(8000)
SET @Output = ''
SELECT @Output = @Output + prod_desc + ', '
FROM prod
WHERE product_id = @product_id
ORDER BY prod_desc
RETURN @Output
END
GO
SELECT DISTINCT product_id, prod_desc = left(dbo.Concatdesc(product_id),
len(dbo.Concatdesc(product_id))-1)
FROM prod
ORDER BY product_id
Remi's script incorporated in option 1, worked well. Thanks. I understand using an UDF is efficient - how does this compare to Farrell's method of using a WHILE loop.
-
A WHILE loop will probably take longer, although the function will still have walk through each record to get the output you want.
I coded mine specifically so the function-like activity could be seen. I would recommend staying with Remi's function approach as you will find you will probably need other similar functions to process what you want. Functions are re-useable is coded well. My approach is a one time shot for that specific use.
I wasn't born stupid - I had to study.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply