October 2, 2006 at 5:38 am
Hi
I have a view that displays data like this:
company_id category data
619 Investment Managers 'aaa'
619 Investment Managers 'bbb'
619 Investment Managers 'ccc'
1070 Investment Managers 'aaa'
1070 Investment Managers 'bbb'
1070 Investment Managers 'ccc'
1070 Investment Managers 'ddd'
I would like to display data like this:
company_id category data
619 Investment Managers 'aaa,bbb,ccc'
1070 Investment Managers 'aaa,bbb,ccc,ddd'
does anyone know a method to achieve this?
thanks
Darren
October 2, 2006 at 6:22 am
If data consists of fixed values then use CASE statements and GROUP BY
If data consists of variable values then create a function to concatenate data for and use it for each unique company_id, category
If you are on SQL 2005 then use PIVOT (either fixed columns or dynamic sql to generate columns)
Far away is close at hand in the images of elsewhere.
Anon.
October 2, 2006 at 7:41 am
Try this out (You can modify this T-SQL according to your need)
DECLARE @tbl TABLE
(RowId VARCHAR(20),
RValue VARCHAR(30),
Lineage VARCHAR(3000) DEFAULT '')
INSERT INTO @tbl(RowId,RValue)
SELECT 1,'1A' UNION SELECT 1,'1B' UNION SELECT 1,'1C' UNION SELECT 1,'1D' UNION SELECT 2,'2A' UNION SELECT 2,'2B' UNION SELECT 2,'2C' UNION SELECT 3,'3A' UNION SELECT 3,'3B'
SELECT * FROM @tbl
DECLARE @RowId VARCHAR(20)
DECLARE @val VARCHAR(3000)
DECLARE @Cnt INT
SELECT @Val = ''
UPDATE Source
SET @RowId = CASE WHEN @RowId IS NULL THEN Source.RowId ELSE @RowId END,
@Val = Source.Lineage = CASE WHEN @RowId = Source.RowId THEN @val+ISNULL(Source.RValue,'NULL')+',' ELSE ISNULL(Source.RValue,'NULL')+',' END,
@Cnt = CASE WHEN @RowId = Source.RowId THEN @Cnt+1 ELSE 1 END,
@RowId = Source.RowId
FROM @tbl AS Source
SELECT RowId,
SUBSTRING(MAX(Lineage),1,LEN(MAX(Lineage))-1) AS Lineage
FROM @tbl
GROUP BY RowId
Ram
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply