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.
| Author |
Topic |
|
ryoka12
Starting Member
9 Posts |
 Posted - 2015-01-21 : 23:30:40
|
Hi please help me on how can i accomplish this.
Below is my sample data.
My problem is how can i separate the two id the '1' and '2' with their different description.
I have extracted the Description on as one string.
[code]
DECLARE @Description VARCHAR(max)
SELECT @Description = COALESCE(@Description + '', '') +
ISNULL(Description , ' ')
FROM Table1
[code]
[img]
ID Description
1 the quick
1 brown fox
1 jump over
1 the lazy
1 dog.
2 humpty dumpty
2 sat on the wall
2 humpty dumpty
2 had a great fall.
2 Threescore men
2 and threescore more,
2 Cannot place humpty
2 dumpty as he was before.
[/img]
Output:
ID Description
1 the quick brown fox jump over the lazy dog.
2 humpty dumpty had a great fall.Threescore men and threescore more, Cannot place humpty dumpty as he was before.
Thank you very much.
|
|
|
rocknpop
Posting Yak Master
201 Posts |
Posted - 2015-01-23 : 01:14:29
|
Assuming the delimiter to be a semi-colon (or you can choose any other character), as you have comma in the string:
DECLARE @STR VARCHAR(100) = 'The quick brown fox jump over the lazy dog;humpty dumpty had a great fall.Threescore men and threescore more, Cannot place humpty dumpty as he was before.'
SELECT T.C.value('.', 'VARCHAR(100)')
FROM (SELECT CAST('<ROOT>' + REPLACE(@STR,';','</ROOT><ROOT>') + '</ROOT>' AS XML)AS COL)TAB
CROSS APPLY TAB.COL.nodes('/ROOT')T(C)
--------------------
Rock n Roll with SQL |
 |
|
|
ryoka12
Starting Member
9 Posts |
Posted - 2015-01-23 : 01:56:34
|
Hi,
Thank you for your reply and it works.
What if the scenario is below.
ID Description
1 The quick
1 brown fox
1 jump over
1 the lazy
1 dog.
2 Humpty Dumpty
2 had a great
2 fall. Threescore
2 men and threescore
2 more, Cannot place
2 humpty dumpty
2 as he was before.
How can i Seperate the two id and get the corresponding description.
Thank you again. |
|
|
|
rocknpop
Posting Yak Master
201 Posts |
Posted - 2015-01-23 : 04:23:00
|
Try this, please note the delimiters used and assuming the string is in the following format:
DECLARE @STR VARCHAR(100) = '1-The quick brown fox jump over the lazy dog;2-humpty dumpty had a great fall.Threescore men and threescore more, Cannot place humpty dumpty as he was before.'
;WITH CTE
AS(
SELECT T.C.value('.', 'VARCHAR(100)') AS COL
FROM (SELECT CAST('<ROOT>' + REPLACE(@STR,';','</ROOT><ROOT>') + '</ROOT>' AS XML)AS COL)TAB
CROSS APPLY TAB.COL.nodes('/ROOT')T(C)
)
SELECT SUBSTRING(COL,1,CHARINDEX('-',COL)-1) AS ID,
SUBSTRING(COL,CHARINDEX('-',COL)+1,LEN(COL)) AS DESCRIPTION,
*
FROM CTE
--------------------
Rock n Roll with SQL |
|
|
|
ryoka12
Starting Member
9 Posts |
Posted - 2015-01-23 : 05:40:02
|
Hi,
Again thank you very much..
when i was testing.
then it occurred to me, what if there is no delimiter whatsoever, just the ID that differentiate the description.
How can I still get the description base on the Column?
Thanks for your understanding.
|
|
|
|
rocknpop
Posting Yak Master
201 Posts |
Posted - 2015-01-23 : 05:46:07
|
Can you post your actual string format?
--------------------
Rock n Roll with SQL |
|
|
|
ryoka12
Starting Member
9 Posts |
Posted - 2015-01-26 : 02:19:29
|
Thank you @rocknpop for the reply.
Below is my actual sample string.
Which i must get the Description base on my identifier
and group in a string.
Base in below code
DECLARE @Description VARCHAR(max)
SELECT @Description = COALESCE(@Description + '', '') +
ISNULL(Description , ' ')
FROM Table1
ID Identifier Description
1 1 The quick
2 1 brown fox
3 1 jump over
4 1 the lazy
5 1 dog.
6 2 Humpty Dumpty
7 2 had a great
8 2 fall. Threescore
9 2 men and threescore
10 2 more, Cannot place
11 2 humpty dumpty
12 2 as he was before. |
|
|
|
rocknpop
Posting Yak Master
201 Posts |
Posted - 2015-01-27 : 00:58:35
|
hope this works for you:
DECLARE @TBL TABLE (ID INT IDENTITY(1,1), IDENTIFIER INT, DESCRIPTION VARCHAR(100))
INSERT INTO @TBL(Identifier, Description)
VALUES
(1 ,'The quick'),
( 1 ,'brown fox'),
( 1 ,'jump over'),
( 1 ,'the lazy'),
( 1 ,'dog.'),
( 2 ,'Humpty Dumpty'),
( 2 ,'had a great'),
( 2 ,'fall. ThreescoreV'),
( 2 ,'men and threescore'),
( 2, 'more, Cannot place'),
( 2, 'humpty dumpty'),
( 2, 'as he was before.')
SELECT IDENTIFIER , STUFF(
(SELECT ' ' + DESCRIPTION
FROM @TBL WHERE IDENTIFIER=T.IDENTIFIER
FOR XML PATH('')
),1,1,' '
) AS DESCRIPTION
FROM @TBL t
GROUP BY IDENTIFIER
--------------------
Rock n Roll with SQL |
|
|
|
|
|
|
|
|
How separate and get the ColumnId and Description?