How to do
-
I have the following data in a Table.
I wish to have ouput as such,
ID Text
12 ABC-XYZDEF
13 ABCDEF
14 ABC
In other words for each Unique ID we need to concatenate the text field for the amount of rows in the RowNumber Column.
ID RowNumber Text
12 1 ABC-
12 2 XYZ
12 3 DEF
13 1 ABC
13 2 DEF
14 1 ABC
-
why not do this at application level instead?? It'll simplifies things alot.
Cheers ;),
Jon
PS: Its possible to do it with T-SQL but i wouldn't recommend that, you can do it with or w/o cursor.
-
Hi
This sample statement will generate the concatenated values for id = 12.
Declare @abc varchar(100)
SET @abc = ''
SELECT @abc = @abc + Text from table1 where id = 12.
select @abc.
you can loop for all the id's in the table and generate the resultset you want. I guess you will have to store the concatenated values and id in a temp table while you loop.
Cant think of anything else now...
"Keep Trying"
-
:w00t: its not that i don't know the solution, i'm just trying to discourage what i would classify to be application logic at the database tier..
here's a working syntax.. as i mentioned earlier this is a non-cursor method alternatively you can use cursor also..
DECLARE @MyTable TABLE
(
ID INT,
RowNumber INT,
RowText NVARCHAR(20)
)
INSERT INTO @MyTable
SELECT 12, 1, 'ABC-' UNION
SELECT 12, 2, 'XYZ' UNION
SELECT 12, 3, 'DEF' UNION
SELECT 13, 1, 'ABC' UNION
SELECT 13, 2, 'DEF' UNION
SELECT 14, 3, 'ABC'
DECLARE @TempTable TABLE
(
ID INT,
RowText NVARCHAR(20)
)
DECLARE @Pos AS INT, @iID AS NVARCHAR(100)
DECLARE @rNum AS NVARCHAR(1000)
SET @rNum = ''
DECLARE @idList AS NVARCHAR(3000)
SET @idList=''
SELECT @idList= @idList + CAST(ID AS NVARCHAR(100)) + ',' FROM @MyTable
IF LEN(@idList) > 0
BEGIN
SET @idList = LEFT(@idList,LEN(@idList)-1)
END
PRINT @idList
IF @idList IS NOT NULL
BEGIN
SET @idList = LTRIM(RTRIM(@idList))+ ','
SET @Pos = CHARINDEX(',', @idList, 1)
IF REPLACE(@idList, ',', '') <> ''
BEGIN
WHILE @Pos > 0
BEGIN
SET @iID = LTRIM(RTRIM(LEFT(@idList, @Pos - 1)))
SET @rNum = ''
IF @iID <> ''
BEGIN
SELECT @rNum = @rNum + CAST(RowText AS NVARCHAR(20)) FROM @MyTable WHERE (ID = CAST(@iID AS INT)) ORDER BY RowNumber
IF LEN(@rNum) > 0
BEGIN
SET @rNum = LEFT(@rNum,LEN(@rNum)-1)
END
INSERT INTO @TempTable (ID,RowText) VALUES (CAST(@iID AS INT), CAST(@rNum AS NVARCHAR(20)))
END
SET @idList = RIGHT(@idList, LEN(@idList) - @Pos)
SET @Pos = CHARINDEX(',', @idList, 1)
END
END
END
SELECT DISTINCT * FROM @TempTable
Cheers ;),
Jon
-
Chirag,
I have performed your solution with a cursor (Code below) however is there a way without a cursor? Jon has a method but it's very long winded and complex, surely there is an easier way?
SET NOCOUNT ON
DECLARE @UID VARCHAR(10)
CREATE TABLE #Test
(
UIDVARCHAR(10),
UIDText VARCHAR(200)
)
DECLARE ConcatCuror CURSOR FOR
SELECT DISTINCT UID
FROM UIDTest
OPEN ConcatCuror
FETCH NEXT FROM ConcatCuror INTO @UID
WHILE (@@FETCH_STATUS) <> -1
BEGIN
DECLARE @abc VARCHAR(100)
SET @abc = ''
FROM UIDTest
WHERE UID= @UID
INSERT #Test (UID, UIDText)
VALUES (@UID, @abc)
FETCH NEXT FROM ConcatCuror INTO @UID
END
CLOSE ConcatCuror
DEALLOCATE ConcatCuror
SELECT * FROM #Test
DROP TABLE #Test
-
Hi
i dont think we can avoid a loop in this case. However you can use a while loop instead of a cursor. Try and avoid cursors as much as possible.
"Keep Trying"
-
Here's another way
DECLARE @MyTable TABLE
(
ID INT,
RowNumber INT,
RowText NVARCHAR(20)
)
INSERT INTO @MyTable
SELECT 12, 1, 'ABC-' UNION
SELECT 12, 2, 'XYZ' UNION
SELECT 12, 3, 'DEF' UNION
SELECT 13, 1, 'ABC' UNION
SELECT 13, 2, 'DEF' UNION
SELECT 14, 3, 'ABC'
SELECT ID,
(SELECT t2.RowText AS "text()"
FROM @MyTable t2
WHERE t2.ID=t1.ID
ORDER BY t2.RowNumber
FOR XML PATH(''))
FROM @MyTable t1
GROUP BY t1.ID
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Hey MarkC guess you made me realise i should start reading up on SQL XML
Cheers ;),
Jon
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply