August 18, 2006 at 8:54 am
Hi there,
My question is given a table
create table a (ID int, path varchar(50))
insert into a values (1, 'A')
insert into a values (1, 'B')
insert into a values (1, 'C')
insert into a values (2, 'D')
insert into a values (2, 'E)
insert into a values (2, 'F')
insert into a values (3, 'G')
insert into a values (3, 'H)
I would like to have the following result:
ID path
-- -------
1 A, B, C
2 D, E, F
3 G, H
It should be done without temp table, procedure, function... because it should be used on other databases like DB2 as well
Any idea?
Bye
Gabor
August 18, 2006 at 10:11 am
Not sure about DB2 but this solution is fairly 'ansi'. And should be alot more efficient than a looping structure.
------------------------------------
if object_id('a') is not null -- Drop Test Table
drop table a
--------------DDL-----------------------
create table a (ID int, path varchar(50))
insert into a values (1, 'A')
insert into a values (1, 'B')
insert into a values (1, 'C')
insert into a values (2, 'D')
insert into a values (2, 'E')
insert into a values (2, 'F')
insert into a values (3, 'G')
insert into a values (3, 'H')
go
---------------Add Column to store denormalised values------
alter table a
add allpaths varchar(4000)
go
-------------------Update new column on row by row incremental basis----
declare @id int , @path varchar(10)
update a
set @path = case when @id = id then @path +','+path else path end , allpaths = @path, @id = id
from a
-------------Update all row to be complete list----------------------------
update a
set allpaths = x.allpaths
from a
join (select max(allpaths)allpaths, id from a group by id)x
on a.id = x.id
select distinct id, allpaths from a
drop table a
August 18, 2006 at 1:33 pm
Jules,
Merci. But I only have read only access to the database. So I ndd a select statement. CTE is OK as well
Bye
Gabor
August 18, 2006 at 2:49 pm
What stops you from doing it in application?
_____________
Code for TallyGenerator
August 18, 2006 at 7:53 pm
Gabor,
I know you only have read access but can you convince the DBA to install a function for you because that's the best way to lick this problem...
--Jeff Moden
August 19, 2006 at 8:20 am
August 19, 2006 at 10:15 am
This question is being answered on this forum several times every week.
_____________
Code for TallyGenerator
August 19, 2006 at 11:01 am
Like this... (self supporting test includes function creation, test data creation, and demo of how to use the function).
--=========================================================
-- Create the function to do the concatenization
--=========================================================
--===== If the function already exists, drop it
IF OBJECT_ID('dbo.fnJBMTest') IS NOT NULL
DROP FUNCTION dbo.fnJBMTest
GO
--===== Declare the I/O variables
CREATE FUNCTION dbo.fnJBMTest
(@pID INT) -- Parameter holds ID to concat path for
RETURNS VARCHAR(8000)
AS
BEGIN
--===== Declare local variables
DECLARE @rCSVPath VARCHAR(8000)
--===== Concat all paths for given ID using set
-- based loop
SELECT @rCSVPath = ISNULL(@rCSVPath+',','')+Path
FROM dbo.JBMTest WITH (NOLOCK)
WHERE ID = @pID
RETURN @rCSVPath
END
GO
--=========================================================
-- Test setup, create the test data
--=========================================================
SET NOCOUNT ON
--===== If the test table exists, drop it
IF OBJECT_ID('dbo.JBMTest') IS NOT NULL
DROP TABLE dbo.JBMTest
--===== Create and populate the test table
CREATE TABLE dbo.JBMTest
(
RowNum INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
ID INT,
Path VARCHAR(5)
)
INSERT INTO dbo.JBMTest (ID,Path)
SELECT 1, 'A' UNION ALL
SELECT 1, 'B' UNION ALL
SELECT 1, 'C' UNION ALL
SELECT 2, 'D' UNION ALL
SELECT 2, 'E' UNION ALL
SELECT 2, 'F' UNION ALL
SELECT 3, 'G' UNION ALL
SELECT 3, 'H'
--=========================================================
-- Demo how to use the function to produce a
-- CSV concatenated list of Paths for each ID
-- NOTICE HOW SIMPLE THE FUNCTION MADE THINGS!!!
--=========================================================
SELECT DISTINCT
ID,
AllPaths = dbo.fnJBMTest(ID)
FROM dbo.JBMTest WITH (NOLOCK)
--Jeff Moden
August 19, 2006 at 11:10 am
p.s. Just in case anyone asks...
A function, like this one, should never be used to populate a permanent table because of Normalization Rules. It can, however be used to populate a "semi-static" reporting table so that multiple people can easily "read" the report without having to regen the report everytime someone wants to view it or use it.
I gotta stress it... creating a CSV column in a permanent table is "Death by SQL".
--Jeff Moden
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply