Help Making a Query- URGENT

Question

Post reply

Help Making a Query- URGENT

Mr. Kapsicum

SSCertifiable

Points: 6128
More actions
December 16, 2013 at 9:50 pm

#301467

Hi All,
I have a Table say, 'TABLE_1'. with follwing data (Dummy Data)
RatingIDParameter_Order ProjectKeyComments
792 0 251NULL
792 1 251quality has been improved of all deliverables, got appreciation from partner
792 2 251all milestones on track
792 3 251discussion in progress for salesforce team expansion team expansion plan for 2 .net resources
792 4 251no infrastructure issues
792 5 251team working on multiple business requirements and meeting all expectations of partner
792 6 251dev and qa processes are being followed in aligned with partner
827 0 598NULL
827 1 598quality has been consistent. no escalation from partner/stakeholders
827 2 598working on release called crane
827 3 598one attrition in teach support team
827 4 598this has been consistent
827 5 598we are all well conncted with delivery stake holders.
827 6 598this has been consistent
Now, As mentioned there are two ratingid 792,827.
Each Rating Id has 7 rows of data , with parameter_order from 0 to 6.
For,Parameter_order 1 to 6 i have comments and for 0 parameter_order i have NULL(or there may be '' in the column).
Now I want the column "comments" for paramete_rorder=0 to have all the concatenate value(1 t0 6) for its respective RatingID

Viewing 10 posts - 1 through 9 (of 9 total)

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

ParulPrabu SSCrazy Points: 2271 More actions · Answer 1

ParulPrabu

SSCrazy

Points: 2271

December 16, 2013 at 10:08 pm

#1673942

What is the expected result from this data?.

Mr. Kapsicum SSCertifiable Points: 6128 More actions · Answer 2

RatingIDParameter_Order ProjectKeyComments

792 0 251A.quality has been improved of all deliverables, got appreciation from partner;B.all milestones on track;C.discussion in progress for salesforce team expansion team expansion plan for 2 .net resources;D.no infrastructure issue;E.team working on multiple business requirements and meeting all expectations of partner;F.dev and qa processes are being followed in aligned with partner

792 1 251quality has been improved of all deliverables, got appreciation from partner

792 2 251all milestones on track

792 3 251discussion in progress for salesforce team expansion team expansion plan for 2 .net resources

792 4 251no infrastructure issues

792 5 251team working on multiple business requirements and meeting all expectations of partner

792 6 251dev and qa processes are being followed in aligned with partner

827 0 598A.quality has been consistent. no escalation from partner/stakeholders;B.working on release called crane;C.one attrition in teach support team;D.this has been consistent;E.we are all well connected with delivery stake holders.;F.this has been consistent;

827 1 598quality has been consistent. no escalation from partner/stakeholders

827 2 598working on release called crane

827 3 598one attrition in teach support team

827 4 598this has been consistent

827 5 598we are all well connected with delivery stake holders.

827 6 598this has been consistent

Actually For RatingID 792 and Parameter_order=0, The Comments column has been updated with Comments of Parameter_id between 1 to 6.

And Similarly for Rating 827.

ParulPrabu SSCrazy Points: 2271 More actions · Answer 3

Hi,

Try with query

update table_1 set Comments = SUBSTRING((select ', '+ Comments from table_1 itable

where itable.RatingID = otable.RatingID and Parameter_Order!=0 for xml path('')),3,1000)

from table_1 otable where Parameter_Order =0

Mr. Kapsicum SSCertifiable Points: 6128 More actions · Answer 4

Mr. Kapsicum

SSCertifiable

Points: 6128

December 17, 2013 at 12:44 am

#1673967

can any one suggest how to do this by Recursive CTE.?

Luis Cazares SSC Guru Points: 183698 More actions · Answer 5

Mr. Kapsicum (12/17/2013)
can any one suggest how to do this by Recursive CTE.?

Why would you want to loose performance instead of using a nice and clear method? 😀

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Mr. Kapsicum SSCertifiable Points: 6128 More actions · Answer 6

Luis Cazares (12/17/2013)
Mr. Kapsicum (12/17/2013)
can any one suggest how to do this by Recursive CTE.?
Why would you want to loose performance instead of using a nice and clear method? 😀

can u please explain how this query is getting executed....?> i m confused with this FOR XML PATH use....!!!

Thanks in advance 🙂

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 7

FOR XML is an option to have the results of a query be formatted as an XML component. You'll find the description in Books Online for all the different things it can do.

PATH is an option to the FOR XML statement, meaning that you want to build this query component for a particular tag, where all the included items are then attributes. A PATH of '' means that there's no tag created. A PATH of 'TEST' would look like <TEST Atrribute=Value>.

The lack of a column name of @something on the SELECT statement means that there's no attribute component included, just the result of the data. Don't try to do that with more than one column in the SELECT results.

The SUBSTRING simply strips off the comma that's going to head the result set once all of that is concatonated in a single line.

To see more of what each item there does, play with it. Remove pieces, add pieces, change pieces. To really understand what it's doing though you have to dig deeply into the FOR XML settings and expectations.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Mitesh Oswal SSCrazy Points: 2798 More actions · Answer 8

Just In case you want to use CTE

DECLARE @tbl TABLE

(

RatingID INT,

Parameter_Order INT,

ProjectKey INT,

Comments NVARCHAR(MAX)

)

INSERT INTO @tbl

SELECT 792 , 0, 251,'A.quality has been improved of all deliverables, got appreciation from partner;B.all milestones on track;C.discussion in progress for salesforce team expansion team expansion plan for 2 .net resources;D.no infrastructure issue;E.team working on multiple business requirements and meeting all expectations of partner;F.dev and qa processes are being followed in aligned with partner'

UNION

SELECT 792 , 1, 251,'quality has been improved of all deliverables, got appreciation from partner'

UNION

SELECT 792 , 2, 251,'all milestones on track'

UNION

SELECT 792 , 3, 251,'discussion in progress for salesforce team expansion team expansion plan for 2 .net resources'

UNION

SELECT 792 , 4, 251,'no infrastructure issues'

UNION

SELECT 792 , 5, 251,'team working on multiple business requirements and meeting all expectations of partner'

UNION

SELECT 792 , 6, 251,'dev and qa processes are being followed in aligned with partner'

UNION

SELECT 827 , 0, 598,'A.quality has been consistent. no escalation from partner/stakeholders;B.working on release called crane;C.one attrition in teach support team;D.this has been consistent;E.we are all well connected with delivery stake holders.;F.this has been consistent;'

UNION

SELECT 827 , 1, 598,'quality has been consistent. no escalation from partner/stakeholders'

UNION

SELECT 827 , 2, 598,'working on release called crane'

UNION

SELECT 827 , 3, 598,'one attrition in teach support team'

UNION

SELECT 827 , 4, 598,'this has been consistent'

UNION

SELECT 827 , 5, 598,'we are all well connected with delivery stake holders.'

UNION

SELECT 827 , 6, 598,'this has been consistent'

;WITH CTE AS

(

SELECT RatingID ,MAX(Parameter_Order) Parameter_Order1

FROM @tbl

GROUP BY RatingID

)

, CTE2

as

(

SELECT t.RatingID ,Parameter_Order ,ProjectKey ,Comments,0 AS CurrentID ,

Parameter_Order1

FROM @tbl t

INNER JOIN CTE ON t.RatingID = cte.RatingID

WHERE Parameter_Order = 0

UNION ALL

SELECT CTE2.RatingID ,CTE2.Parameter_Order ,CTE2.ProjectKey ,CTE2.Comments+'||'+t.Comments,CTE2.CurrentID+1 AS CurrentID,

CTE2.Parameter_Order1

FROM CTE2

INNER JOIN @tbl t ON CTE2.RatingID = t.RatingID

WHERE t.Parameter_Order = CTE2.CurrentID+1

AND CTE2.CurrentID <=Parameter_Order1

)

SELECT * FROM CTE2 where currentid = Parameter_Order1

Regards,
Mitesh OSwal
+918698619998

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 9

Mr. Kapsicum (12/16/2013)
Hi All,
I have a Table say, 'TABLE_1'. with follwing data (Dummy Data)
RatingIDParameter_Order ProjectKeyComments
792 0 251NULL
792 1 251quality has been improved of all deliverables, got appreciation from partner
792 2 251all milestones on track
792 3 251discussion in progress for salesforce team expansion team expansion plan for 2 .net resources
792 4 251no infrastructure issues
792 5 251team working on multiple business requirements and meeting all expectations of partner
792 6 251dev and qa processes are being followed in aligned with partner
827 0 598NULL
827 1 598quality has been consistent. no escalation from partner/stakeholders
827 2 598working on release called crane
827 3 598one attrition in teach support team
827 4 598this has been consistent
827 5 598we are all well conncted with delivery stake holders.
827 6 598this has been consistent
Now, As mentioned there are two ratingid 792,827.
Each Rating Id has 7 rows of data , with parameter_order from 0 to 6.
For,Parameter_order 1 to 6 i have comments and for 0 parameter_order i have NULL(or there may be '' in the column).
Now I want the column "comments" for paramete_rorder=0 to have all the concatenate value(1 t0 6) for its respective RatingID

Ok... you've been around long enough where you should know to post readily consumable data especially for "urgent" requests. If you don't know how to easily do that, please see the first link under "Helpful Links" in my signature line below. Give your question a better chance. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)