How separate and get the ColumnId and Description? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2005 Forums

Transact-SQL (2005)

How separate and get the ColumnId and Description?

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources