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


Help Making a Query- URGENT


Help Making a Query- URGENT

Author
Message
Mr. Kapsicum
Mr. Kapsicum
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1997 Visits: 1037
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
parulprabu
parulprabu
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1602 Visits: 136
What is the expected result from this data?.
Mr. Kapsicum
Mr. Kapsicum
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1997 Visits: 1037
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.
parulprabu
parulprabu
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1602 Visits: 136
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
Mr. Kapsicum
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1997 Visits: 1037
can any one suggest how to do this by Recursive CTE.?
Luis Cazares
Luis Cazares
SSC-Forever
SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)

Group: General Forum Members
Points: 41692 Visits: 19815
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? :-D


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
Mr. Kapsicum
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1997 Visits: 1037
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? :-D



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
Evil Kraig F
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20433 Visits: 7660
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
Mitesh Oswal
Mitesh Oswal
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1170 Visits: 653
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
Jeff Moden
SSC Guru
SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)

Group: General Forum Members
Points: 213607 Visits: 41977
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
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