SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Group the common word


Group the common word

Author
Message
Krtyknm
Krtyknm
Old Hand
Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)

Group: General Forum Members
Points: 362 Visits: 374
Hey,

Can you please provide some on idea on group the common word?

Example:

Code Desc
A1 AAAA BBBB CCCC
A1.1 AAAA BBBB CCCC DDDDD
A1.2 AAAA BBBB CCCC DDDDD EEEEE
A2 XXXX
A2.1 XXXX YYYY
A2.3 XXXX YYYY ZZZZ

Output will be

Code Desc Desc2 Desc3 Desc4
A1 AAAA BBBB CCCC AAAA BBBB CCCC
A1.1 AAAA BBBB CCCC DDDDD AAAA BBBB CCCC DDDDD
A1.2 AAAA BBBB CCCC DDDDD EEEEE AAAA BBBB CCCC DDDDD EEEEE
A2 XXXX XXXX
A2.1 XXXX YYYY XXXX YYYY
A2.3 XXXX YYYY ZZZZ XXXX YYYY ZZZZ

Thanks,
Karthik
ColdCoffee
ColdCoffee
SSCrazy Eights
SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)

Group: General Forum Members
Points: 8741 Visits: 5555
You said grouping, but the output shows replicating the existing information? Did you really mean that or am I missing something?
Phil Parkin
Phil Parkin
SSC Guru
SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)

Group: General Forum Members
Points: 51310 Visits: 21156
Krtyknm (8/6/2012)
Hey,

Can you please provide some on idea on group the common word?

Example:

Code Desc
A1 AAAA BBBB CCCC
A1.1 AAAA BBBB CCCC DDDDD
A1.2 AAAA BBBB CCCC DDDDD EEEEE
A2 XXXX
A2.1 XXXX YYYY
A2.3 XXXX YYYY ZZZZ

Output will be

Code Desc Desc2 Desc3 Desc4
A1 AAAA BBBB CCCC AAAA BBBB CCCC
A1.1 AAAA BBBB CCCC DDDDD AAAA BBBB CCCC DDDDD
A1.2 AAAA BBBB CCCC DDDDD EEEEE AAAA BBBB CCCC DDDDD EEEEE
A2 XXXX XXXX
A2.1 XXXX YYYY XXXX YYYY
A2.3 XXXX YYYY ZZZZ XXXX YYYY ZZZZ

Thanks,
Karthik



Your input and output seem to contain random numbers of columns. Please provide sample data and results in a more consumable format, as per the link in my signature.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Lowell
Lowell
SSC Guru
SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)

Group: General Forum Members
Points: 71318 Visits: 40930
well this kind of gets you the data chopped up into the pieces youa r elooking for;
i'm using the famous DelimitedSplit8K you'll find links to in many posters signitures, and you can search teh site as well:


With MyCTE( Code,[Desc])
AS
(
SELECT 'A1','AAAA BBBB CCCC' UNION ALL
SELECT 'A1.1','AAAA BBBB CCCC DDDDD' UNION ALL
SELECT 'A1.2','AAAA BBBB CCCC DDDDD EEEEE' UNION ALL
SELECT 'A2','XXXX' UNION ALL
SELECT 'A2.1','XXXX YYYY' UNION ALL
SELECT 'A2.3','XXXX YYYY ZZZZ'
)

select * From MyCTE
cross apply dbo.DelimitedSplit8K([Desc],' ') x



i have no idea what you wanted for your final outoput; it was just not intuitive what you are after.

Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
GSquared
GSquared
SSC Guru
SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)

Group: General Forum Members
Points: 57435 Visits: 9730
It looks to me like you're just repeating the contents of each row. "A B C" becomes "A B C A B C" (I just took out the repeating letters to make it a bit easier to read). Each row seems to do exactly that.

But that doesn't match the columns you listed in your output.

So, I really can't tell what you're trying to do here. If it's just repeat a value, then just add the value (as a string) to itself (as a string), and you'll have it.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Krtyknm
Krtyknm
Old Hand
Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)

Group: General Forum Members
Points: 362 Visits: 374
Hey Guys,

Sorry for providing in incorrect format.

Please find the below.

With MyCTE( Code,[Desc])
AS
(
SELECT 'A1','AAAA BBBB CCCC' UNION ALL
SELECT 'A1.1','AAAA BBBB CCCC DDDDD' UNION ALL
SELECT 'A1.2','AAAA BBBB CCCC DDDDD EEEEE' UNION ALL
SELECT 'A2','XXXX' UNION ALL
SELECT 'A2.1','XXXX YYYY' UNION ALL
SELECT 'A2.3','XXXX YYYY ZZZZ'
)

select * From MyCTE


;With MyCTEOutput( Code,[Desc],[Desc1],Desc2,Desc3)
AS
(
SELECT 'A1','AAAA BBBB CCCC','AAAA BBBB CCCC','','' UNION ALL
SELECT 'A1.1','AAAA BBBB CCCC DDDDD','AAAA BBBB CCCC','DDDDD','' UNION ALL
SELECT 'A1.2','AAAA BBBB CCCC DDDDD EEEEE', 'AAAA BBBB CCCC','DDDDD','EEEEE' UNION ALL
SELECT 'A2','XXXX','','','' UNION ALL
SELECT 'A2.1','XXXX YYYY','XXXX','YYYY','' UNION ALL
SELECT 'A2.3','XXXX YYYY ZZZZ','XXXX','YYYY','ZZZZ'
)

SELECT * FROM MyCTEOutput

Basically, we need to keep the common word in all the rows.

Thanks,
Karthik
GSquared
GSquared
SSC Guru
SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)

Group: General Forum Members
Points: 57435 Visits: 9730
Do you know before-hand how many columns you'll have in the final output? If not, you'll need some sort of dynamic SQL to write the query for you. That'll get complex fast!

Alternatively, can the output be in XML format? If so, then the variable "columns" doesn't matter. A recursive For XML query will do what's needed for that.

If it's a fixed number of columns, then you can do this with a recursive CTE. It's just a hierarchy crawl, with variable data in the "repeating sets" columns.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Krtyknm
Krtyknm
Old Hand
Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)

Group: General Forum Members
Points: 362 Visits: 374
Ok. Here the columns will be added dynamically based on the first row and second row description value. If the first description value is AAA BBB CCC and second description value os AAA BBB CCC DDD EEE, then two coulmn will be added for DDD and EEE.

So we need to separate the common words in the column desc for the code A1 (group).

Let me know if it is not make sense.

Thanks,
Karthik
GSquared
GSquared
SSC Guru
SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)

Group: General Forum Members
Points: 57435 Visits: 9730
That makes sense.

That means a variable number of columns, from what I can see. That's fairly complex to code, unless XML is an option for the output.

Will the application that reads this data accept variable columns? Most won't, but it can be built to do so in some cases.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Krtyknm
Krtyknm
Old Hand
Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)

Group: General Forum Members
Points: 362 Visits: 374
Yes, variable columns are accepted in the code.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search