Sting comma Separator

Question

Sting comma Separator

farooq.hbs

SSCrazy

Points: 2083
More actions
June 1, 2012 at 12:38 am

#274859

Hi
I have Values Like this
Project Name WorkType Status TagSetName TagValues
Wrong retainer on Front back Lh FocusTollgate004_ On Track Primary Project Impact Area Quality
Wrong retainer on Front back Lh FocusTollgate004_ On Track Other BBP Metric(s) (Level 2) customer
Wrong retainer on Front back Lh FocusTollgate004_ On Track Other BBP Metric(s) Impacted Quality
Wrong retainer on Front back Lh FocusTollgate004_ On Track Special Initiatives KT - Kepner
Wrong retainer on Front back Lh FocusTollgate004_ On Track Special InitiativesKaizen workshop
In the TagSetName, Special Initiatives is repeated so i want to do tagValues comma separated With Respective tagSetName
Like
Project Name WorkType Status TagSetName TagValues
Wrong retainer on Front back Lh FocusTollgate004_ On Track Special Initiatives KT - Kepner, Kaizen shop
Kaizen workshop
Thanks

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Cadavre SSC-Forever Points: 41690 More actions · Answer 1

farooq.hbs (6/1/2012)
Hi
I have Values Like this
Project Name WorkType Status TagSetName TagValues
Wrong retainer on Front back Lh FocusTollgate004_ On Track Primary Project Impact Area Quality
Wrong retainer on Front back Lh FocusTollgate004_ On Track Other BBP Metric(s) (Level 2) customer
Wrong retainer on Front back Lh FocusTollgate004_ On Track Other BBP Metric(s) Impacted Quality
Wrong retainer on Front back Lh FocusTollgate004_ On Track Special Initiatives KT - Kepner
Wrong retainer on Front back Lh FocusTollgate004_ On Track Special InitiativesKaizen workshop
In the TagSetName, Special Initiatives is repeated so i want to do tagValues comma separated With Respective tagSetName
Like
Project Name WorkType Status TagSetName TagValues
Wrong retainer on Front back Lh FocusTollgate004_ On Track Special Initiatives KT - Kepner, Kaizen shop
Kaizen workshop
Thanks

Have a read of this article[/url] about the best way to post sample data and DDL then please post back with the correct details. That way, the unpaid volunteers of this site will find it far easier to help you.

Thanks.

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

vinu512 SSCoach Points: 15729 More actions · Answer 2

This might help:

--Creating Table

Create Table Ex

(ProjectName varchar(50),

WorkType varchar(50),

Status varchar(50),

TagSetName varchar(50),

TagValues varchar(50) )

--Inserting Sample Data

Insert Into Ex

Select 'Wrong retainer on Front back Lh Focus','Tollgate004_', 'On Track', 'Primary Project Impact Area', 'Quality'

Union ALL

Select 'Wrong retainer on Front back Lh Focus','Tollgate004_', 'On Track', 'Other BBP Metric(s) (Level 2)', 'customer'

Union ALL

Select 'Wrong retainer on Front back Lh Focus','Tollgate004_', 'On Track', 'Other BBP Metric(s) Impacted', 'Quality'

Union ALL

Select 'Wrong retainer on Front back Lh Focus','Tollgate004_', 'On Track', 'Special Initiatives', 'KT - Kepner'

Union ALL

Select 'Wrong retainer on Front back Lh Focus','Tollgate004_', 'On Track', 'Special Initiatives','Kaizen workshop'

--Query For Your Requirement

;With CTE

As

(Select *, ROW_NUMBER() Over (Partition By TagSetName Order By (Select NULL) ) As rn From Ex)

Select a.ProjectName, a.WorkType, a.Status, a.TagSetName, (a.TagValues+', '+b.TagValues) As TagValues From CTE as a

JOIN CTE as b ON a.TagSetName = b.TagSetName AND b.rn = (a.rn + 1)

Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉

ralm Hall of Fame Points: 3156 More actions · Answer 3

This might help as well

;With CTE

As

(Select *, Count(1) Over (Partition By TagSetName) As rn From Ex)

Select a.ProjectName, a.WorkType, a.Status, a.TagSetName,a.TagValues from CTE a where rn =1

union

Select distinct a.ProjectName, a.WorkType, a.Status, a.TagSetName, (Select TagValues+',' from CTE Where rn = a.rn and rn > 1 FOR XML path('')) Skills

From CTE a where rn > 1

^{[font="Verdana"]Regards,
Rals[/font].}

farooq.hbs SSCrazy Points: 2083 More actions · Answer 4

thankz

But the Concatenated row Repeating two Times with same value

With Tag (WorkId,TagsetId,ProjectName,WorkType,Status,ObjectId,TagSetName ,TagValues)

AS

(

select top 5000 vw.work_id as WorkId ,g.tagset_id as TagsetId ,vw.name as ProjectName,

vw.work_type_code as WorkType,vw.status_current as Status,g.object_id as ObjectId,

g.tagset_name as TagSetName,g.tag_name as TagValues

From View_work vw

Left join view_tag g on g.object_id = vw.work_id

Where is_project = 'Y' and --vw.type_name <> 'Folder' and

vw.work_type_code in('Checkpoint','FileFolder','Organization ',

'ReportFolder','Template','Tollgate001_','Tollgate002_','Tollgate003_','Tollgate004_',

'Tollgate005_','Tollgate007_','Work','Work006_','Tollgate000_','FileFolder','GroupFolder','MasterTask','Milestone')

and vw.status_current in('Canceled' ,'Completed','Deferred','Needs Attention','Not Started','Off Track','On Track ')

)

Select ProjectName,WorkType,Status,TagsetId,ObjectId,TagSetName,Stuff(( SELECT ', ' + CAST(Tag_name AS VARCHAR(MAX))

FROM View_tag m where TagsetId = m.Tagset_id and m.Object_id = ObjectId

FOR XML PATH('') ,TYPE

).value('.','VARCHAR(MAX)')

,1,2,'')as TagValues

FROM Tag

ProjectNameWorkTypeStatusTagsetIdObjectIdTagSetNameTagValues

Wrong retainer on Front back Lh FocusTollgate004_ On Track 1801a2g0000ilai7qoa00000001800m380000inot9tu20000000Special InitiativesKT - Kepner Tregoe Project (from Sigma TRAC), Kaizen workshop

[/code]

ralm Hall of Fame Points: 3156 More actions · Answer 5

I used distinct to avoid duplicates.

But I won't suggest that as well 🙂

^{[font="Verdana"]Regards,
Rals[/font].}

farooq.hbs SSCrazy Points: 2083 More actions · Answer 6

Yup Got the Result ...

"Distinct" was not the Issue But I replaced Left Join To Join Keyword in my Query,....

Query Working Fine

ralm Hall of Fame Points: 3156 More actions · Answer 7

ralm

Hall of Fame

Points: 3156

June 1, 2012 at 5:58 am

#1495814

Great !!!

^{[font="Verdana"]Regards,
Rals[/font].}