Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Group the common word Expand / Collapse
Author
Message
Posted Monday, August 06, 2012 10:16 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, April 04, 2014 10:39 PM
Points: 195, Visits: 327
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

Post #1340737
Posted Monday, August 06, 2012 10:24 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, April 07, 2014 3:41 PM
Points: 2,259, Visits: 5,395
You said grouping, but the output shows replicating the existing information? Did you really mean that or am I missing something?
Post #1340743
Posted Monday, August 06, 2012 10:35 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 4:41 AM
Points: 4,828, Visits: 11,180
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.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1340749
Posted Monday, August 06, 2012 10:48 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:46 PM
Points: 12,744, Visits: 31,065
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1340756
Posted Monday, August 06, 2012 11:34 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Monday, April 14, 2014 1:34 PM
Points: 15,442, Visits: 9,588
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
Post #1340781
Posted Monday, August 06, 2012 11:40 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, April 04, 2014 10:39 PM
Points: 195, Visits: 327
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
Post #1340786
Posted Monday, August 06, 2012 12:08 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Monday, April 14, 2014 1:34 PM
Points: 15,442, Visits: 9,588
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
Post #1340810
Posted Monday, August 06, 2012 12:47 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, April 04, 2014 10:39 PM
Points: 195, Visits: 327
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
Post #1340836
Posted Monday, August 06, 2012 12:51 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Monday, April 14, 2014 1:34 PM
Points: 15,442, Visits: 9,588
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
Post #1340839
Posted Monday, August 06, 2012 1:37 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, April 04, 2014 10:39 PM
Points: 195, Visits: 327
Yes, variable columns are accepted in the code.
Post #1340871
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse