1. Registered User
Join Date
Mar 2004
Posts
1

Belows are the data of table T1

field1 field2 field3 Value
F1 F2 F3 A
F1 F2 F3 B
F1 F2 F3 C
... .... .....
F1 F2 F3 Z

Can any bright person help to script the SQL to extract above data set and present as below ???

field1 field2 field3 Value
F1 F2 F3 ABC......Z

Thanks for help

2. Window Washer
Join Date
Nov 2002
Location
Jersey
Posts
10,322
Thought this woiuld be a good exercise...WHY you would want to do this, I have no idea....

Sorry, had to use a cursor.....any non cursor solutions out there?

Code:
```USE Northwind
GO

CREATE TABLE myTable99(Col1 char(2), Col2 char(2), Col3 char(2), Col4 char(1))
GO

INSERT INTO myTable99(Col1, Col2, Col3, Col4)
SELECT 'F1', 'F2', 'F3', 'A' UNION ALL
SELECT 'F1', 'F2', 'F3', 'B' UNION ALL
SELECT 'F1', 'F2', 'F3', 'C' UNION ALL
SELECT 'F1', 'F2', 'F3', 'Z' UNION ALL
SELECT 'F1', 'F2', 'F5', 'A' UNION ALL
SELECT 'F1', 'F2', 'F6', 'B' UNION ALL
SELECT 'F1', 'F2', 'F7', 'C' UNION ALL
SELECT 'F1', 'F2', 'F8', 'Z'
GO

SET NOCOUNT ON

DECLARE myCursor99 CURSOR
FOR
SELECT DISTINCT Col1, Col2, Col3
FROM myTable99

DECLARE @Col1 char(2), @Col2 char(2), @Col3 char(2), @Col4 varchar(7000)

DECLARE @x TABLE(Col1 char(2), Col2 char(2), Col3 char(2), Col4 varchar(7000))

OPEN myCursor99

FETCH NEXT
FROM myCursor99
INTO @Col1
, @Col2
, @Col3

WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @Col4 = COALESCE(@Col4+ ', ', '') + CAST(Col4 AS varchar(6))
FROM myTable99
WHERE Col1 = @Col1
AND Col2 = @Col2
AND Col3 = @Col3

INSERT INTO @x(Col1, Col2, Col3, Col4)
SELECT DISTINCT @Col1, @Col2, @Col3, @Col4
FROM myTable99
WHERE Col1 = @Col1
AND Col2 = @Col2
AND Col3 = @Col3

FETCH NEXT
FROM myCursor99
INTO @Col1
, @Col2
, @Col3

SELECT @Col4 = null
END

CLOSE myCursor99
DEALLOCATE myCursor99

SET NOCOUNT OFF

SELECT * FROM @x

SET NOCOUNT OFF
GO

DROP TABLE myTable99
GO```

3. Registered User
Join Date
Mar 2004
Posts
45
CREATE FUNCTION dbo.f_ConcatVals(@f1 char(2), @f2 char(2), @f3 char(3)) RETURNS varchar(8000) AS BEGIN
DECLARE @s varchar(8000)
SET @s = ''
SELECT @s = @s + Value
FROM T1
WHERE Field1 = @f1 AND Field2 = @f2 AND Field3 = @f3
ORDER BY Value
RETURN @s END

SELECT DISTINCT Field1, Field2, Field3, dbo.f_ConcatVals(Field1,Field2,Field3) Value
FROM t1

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•