SQL Server Performance Forum – Threads Archive

TSQL assistance please

I need to basically rollup some info from a table, and not sure how. Say I have table data that looks like: Col1 Col2 Col3 Col4
———————-
11 A Null Null
11 B C Null
11 Null Null E So, Col1 is the common identifier, and I’d want to select and get: A,B,C,E Please advise….thx a mil!

---

Step 1 is to do create table #t (col1 varchar(10), col2 varchar(10)) INSERT INTO #t
SELECT Col1, Col2 FROM tbl
UNION SELECT Col1, Col3 FROM tbl
UNION SELECT Col1, Col4 FROM tbl Step 2 is to do a cross-tab on your #t temp table. DO a search for cross-tab.

---

thx, is there a way to join the table to itself, and get it into one row that way? (I’m a little tsql challenged <img src=’/community/emoticons/emotion-1.gif’ alt=’‘ />

---

Self join? SELECT t1.*, t2.*
FROM tbl t1
INNER JOIN tbl t2
ON t1.pkcol = t2.pkcol I mixed up my answer – you don’t need a cross-tab but a UDF … CREATE FUNCTION dbo.fnRollUpFromTbl (@KeyID INT)
RETURNS VARCHAR(8000)
AS DECLARE @Return VARCHAR(8000) SET @Return = ” SELECT @Return = @Return + CASE WHEN LEN(@Return) = 0 THEN ” ELSE ‘,’ END + t.Col2
FROM
(SELECT tbl.Col2 FROM tbl WHERE tbl.Col1 = @KeyId AND tbl.Col2 <> ”
UNION SELECT tbl.Col3 FROM tbl WHERE tbl.Col1 = @KeyId AND tbl.Col3 <> ”
UNION SELECT t.Col4 FROM tbl WHERE tbl.Col1 = @KeyId AND tbl.Col4 <> ”) t RETURN (@Return) GO

---

]]>