select format - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

select format

Author

Topic

work2gs
Starting Member

6 Posts

Posted - 2010-05-24 : 10:36:19

Hi

is it possible to write a select that provide for each entry in T1 all the elements corresponding in the second table T2

X,A,a,b
Y,B,c
Z,C

T1 (id=X, name=A)
T1 (id=Y, name=B)
T1 (id=Z, name=C)

and

T2 (id=X, elt=a)
T2 (id=X, elt=b)
T2 (id=Y, elt=c)

Thanks

Fred

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2010-05-24 : 14:44:02

[code]
SELECT t1.ID, t1.name, t2.elt
FROM T1
LEFT OUTER JOIN T2
ON T1.ID = T2.ID[/code]

will result in:
ID,name,elt
X, A, a
X, A, b
Y, B, c
Z, C, NULL

It is preferred to do this in the presentation or service layer of your application, but if you really must concatenate a value in a single column (elt='a,b' for T1.ID=X) from the database, you can create a SQL CLR aggregate:

http://www.mssqltips.com/tip.asp?tip=1691

or write a customer function or stored procedure to do it with this technique:

[code]
-----------------------
-- SETUP SAMPLE DATASET
-----------------------
IF OBJECT_ID('tempdb..#temp_tbl1') IS NOT NULL DROP TABLE #temp_tbl1;
IF OBJECT_ID('tempdb..#tbl1') IS NOT NULL DROP TABLE #tbl1;

CREATE table #tbl1
(
ID INT IDENTITY(0,1) NOT NULL PRIMARY KEY,
TCODE AS CHAR(65 + (ID / 5 % 26)) PERSISTED NOT NULL,
Field1 char(3),
Field1Concat VARCHAR(MAX)
);
GO
INSERT INTO #tbl1 (Field1) SELECT LEFT(CONVERT(VARCHAR(36),NEWID()),3);
GO 301
--select * FROM #tbl1 order by ID

-----------------------
-- CREATE CONCATENATION
-----------------------

-- Setup temp table to store intermediate results
CREATE table #temp_tbl1
(
ID INT,
TCODE CHAR(1),
Field1 CHAR(3),
Field1Concat VARCHAR(MAX)
);
-- Make sure the table is physically ordered by concatenation grouping
CREATE CLUSTERED INDEX ix_#temp_tbl1 ON #temp_tbl1 (TCODE,ID);

INSERT #temp_tbl1 (ID,TCODE,Field1)
SELECT ID,TCODE,Field1
FROM #tbl1
ORDER BY TCODE,Field1;

-- Initialize working variables
DECLARE @LastTCODE CHAR(3);
DECLARE @Field1Concat VARCHAR(MAX);

-- The following update interatively updates the temp resultset Field1Concat in sequence
-- This technique relies on undocumented and thus USE-AT-YOUR-OWN-RISK behavior
UPDATE t
SET @Field1Concat = CASE @LastTCODE WHEN TCODE THEN COALESCE(@Field1Concat + ',','') + Field1 ELSE Field1 END
, Field1Concat = @Field1Concat
, @LastTCODE = TCODE
FROM #temp_tbl1 as t;

-- Return results by selecting the row from each grouping with the maximum length concatenation
WITH cte1
AS
(
SELECT ID,TCODE,Field1Concat, LEN(Field1Concat) AS LEN_Field1Concat, MAX(LEN(Field1Concat)) OVER(PARTITION BY TCODE) AS MAX_LEN_Field1Concat
FROM #temp_tbl1
)
SELECT TCODE,Field1Concat
FROM cte1
WHERE LEN_Field1Concat = MAX_LEN_Field1Concat

-- Cleanup our temp tables
DROP TABLE #temp_tbl1;
DROP TABLE #tbl1;[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources