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

Help Making a Query- URGENT Expand / Collapse
Author
Message
Posted Monday, December 16, 2013 9:50 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, April 03, 2014 10:57 PM
Points: 416, Visits: 512
Hi All,

I have a Table say, 'TABLE_1'. with follwing data (Dummy Data)
RatingID	Parameter_Order	   ProjectKey	Comments
792 0 251 NULL
792 1 251 quality has been improved of all deliverables, got appreciation from partner
792 2 251 all milestones on track
792 3 251 discussion in progress for salesforce team expansion team expansion plan for 2 .net resources
792 4 251 no infrastructure issues
792 5 251 team working on multiple business requirements and meeting all expectations of partner
792 6 251 dev and qa processes are being followed in aligned with partner
827 0 598 NULL
827 1 598 quality has been consistent. no escalation from partner/stakeholders
827 2 598 working on release called crane
827 3 598 one attrition in teach support team
827 4 598 this has been consistent
827 5 598 we are all well conncted with delivery stake holders.
827 6 598 this 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
Post #1523521
Posted Monday, December 16, 2013 10:08 PM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, December 18, 2013 5:03 AM
Points: 1,454, Visits: 135
What is the expected result from this data?.
Post #1523523
Posted Monday, December 16, 2013 10:18 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, April 03, 2014 10:57 PM
Points: 416, Visits: 512
RatingID	Parameter_Order	   ProjectKey	Comments
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
792 1 251 quality has been improved of all deliverables, got appreciation from partner
792 2 251 all milestones on track
792 3 251 discussion in progress for salesforce team expansion team expansion plan for 2 .net resources
792 4 251 no infrastructure issues
792 5 251 team working on multiple business requirements and meeting all expectations of partner
792 6 251 dev and qa processes are being followed in aligned with partner
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;
827 1 598 quality has been consistent. no escalation from partner/stakeholders
827 2 598 working on release called crane
827 3 598 one attrition in teach support team
827 4 598 this has been consistent
827 5 598 we are all well connected with delivery stake holders.
827 6 598 this 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.
Post #1523525
Posted Monday, December 16, 2013 10:21 PM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, December 18, 2013 5:03 AM
Points: 1,454, Visits: 135
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

Post #1523527
Posted Tuesday, December 17, 2013 12:44 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, April 03, 2014 10:57 PM
Points: 416, Visits: 512
can any one suggest how to do this by Recursive CTE.?
Post #1523555
Posted Tuesday, December 17, 2013 9:02 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 6:14 PM
Points: 2,763, Visits: 5,908
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.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1523749
Posted Wednesday, December 18, 2013 11:32 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, April 03, 2014 10:57 PM
Points: 416, Visits: 512
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
Post #1524452
Posted Thursday, December 19, 2013 1:03 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 3:16 PM
Points: 5,986, Visits: 6,931
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 | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1524475
Posted Thursday, December 19, 2013 4:42 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Yesterday @ 7:56 AM
Points: 751, Visits: 583
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
Post #1524518
Posted Wednesday, January 01, 2014 12:12 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:47 PM
Points: 35,959, Visits: 30,252
Mr. Kapsicum (12/16/2013)
Hi All,

I have a Table say, 'TABLE_1'. with follwing data (Dummy Data)
RatingID	Parameter_Order	   ProjectKey	Comments
792 0 251 NULL
792 1 251 quality has been improved of all deliverables, got appreciation from partner
792 2 251 all milestones on track
792 3 251 discussion in progress for salesforce team expansion team expansion plan for 2 .net resources
792 4 251 no infrastructure issues
792 5 251 team working on multiple business requirements and meeting all expectations of partner
792 6 251 dev and qa processes are being followed in aligned with partner
827 0 598 NULL
827 1 598 quality has been consistent. no escalation from partner/stakeholders
827 2 598 working on release called crane
827 3 598 one attrition in teach support team
827 4 598 this has been consistent
827 5 598 we are all well conncted with delivery stake holders.
827 6 598 this 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." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1526935
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse