Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Select query


Select query

Author
Message
p.ravirao
p.ravirao
SSC Rookie
SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)

Group: General Forum Members
Points: 34 Visits: 23
Hi All,
I have two coulmns called RequestID, ApprovalName

ex: RequestID ApprovalName
Row 1: 15 Manager
Row2: 15 Director
row 3: 16 Manager
row4 : 16 Director
row5 : 16 Procurement Head

Now I want show data like below

Row1: 15 Manager,Director
row2: 16 Manager,Director,Procurement Head

Can anyone help to write the query?
Cadavre
Cadavre
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2914 Visits: 8448
How about this?

--Start by making some test data
DECLARE @TABLE AS TABLE(
[RequestID] INT,
[ApprovalName] VARCHAR(35))

INSERT INTO @TABLE([RequestID],[ApprovalName])
SELECT 15, 'Manager'
UNION ALL SELECT 15, 'Director'
UNION ALL SELECT 16, 'Manager'
UNION ALL SELECT 16, 'Director'
UNION ALL SELECT 16, 'Procurement Head'

--Now to the query
;WITH cte ( [RequestID], [Approval List], [ApprovalName], length )
AS (SELECT [RequestID],
CAST('' AS VARCHAR(8000)),
CAST('' AS VARCHAR(8000)),
0
FROM @TABLE
GROUP BY [RequestID]
UNION ALL
SELECT p.[RequestID],
CAST([Approval List] + CASE
WHEN length = 0 THEN ''
ELSE ', '
END + p.[ApprovalName] AS VARCHAR(8000)),
CAST(p.[ApprovalName] AS VARCHAR(8000)),
length + 1
FROM cte c
INNER JOIN @TABLE p
ON c.[RequestID] = p.[RequestID]
WHERE p.[ApprovalName] > c.[ApprovalName])
SELECT [RequestID],
[Approval List]
FROM (SELECT [RequestID],
[Approval List],
Rank() OVER ( PARTITION BY [RequestID] ORDER BY length DESC )
FROM cte) d ( [RequestID], [Approval List], rank )
WHERE rank = 1



Output
/*
RequestID Approval List
----------- -------------------------------------
15 Director, Manager
16 Director, Manager, Procurement Head
*/




Forever trying to learn

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/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


Craig Wilkinson - Software Engineer
LinkedIn
Lowell
Lowell
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18209 Visits: 39425
turning the related rows into a comma delimited list is what you are after right?
the easiest way is to use a neat trick using FOR XML.
here's a nice example I keep handy in my snippets that i think is very intuitive.

declare @skills table (Resource_Id int, Skill_Id varchar(20))
insert into @skills
select 101, 'sqlserver' union all
select 101, 'vb.net' union all
select 101, 'oracle' union all
select 102, 'sqlserver' union all
select 102, 'java' union all
select 102, 'excel' union all
select 103, 'vb.net' union all
select 103, 'java' union all
select 103, 'oracle'
---
select * from @skills s1
--- Concatenated Format
set statistics time on;
SELECT Resource_Id,stuff(( SELECT ',' + Skill_Id
FROM @skills s2
WHERE s2.Resource_Id= s1.resource_ID --- must match GROUP BY below
ORDER BY Skill_Id
FOR XML PATH('')
),1,1,'') as [Skills]
FROM @skills s1
GROUP BY s1.Resource_Id --- without GROUP BY multiple rows are returned
ORDER BY s1.Resource_Id
set statistics time off;



if you need more specific help, we need more specific data from you. CREATE TABLE definitions, example data as an INSERT INTO format like i provided in my example are required.

Lowell

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Cadavre
Cadavre
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2914 Visits: 8448
Lowell (7/14/2010)
turning the related rows into a comma delimited list is what you are after right?
the easiest way is to use a neat trick using FOR XML.
here's a nice example I keep handy in my snippets that i think is very intuitive.


Cool, haven't come across this before. Much easier than the CTE I went with.


Forever trying to learn

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/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


Craig Wilkinson - Software Engineer
LinkedIn
p.ravirao
p.ravirao
SSC Rookie
SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)

Group: General Forum Members
Points: 34 Visits: 23
Thanks a lot!! ..much appriciated for your help..its perfect..
p.ravirao
p.ravirao
SSC Rookie
SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)

Group: General Forum Members
Points: 34 Visits: 23
Hi,
I want write one more select query like below:

I have written below query to get the total of each indicual items with same RequestID(requestitemsID are different)
select
(ts_a.CostLocal * ts_a.Qty)*(ts_b.ExchangeRate)) as Total_Dollor from ts_a ,ts_b
Ex:
RequestID RequestItemID CostLocal Qty Total_doller
331 1036 123.0000 2 5.05776
331 1037 100.0000 3 6.168

Now i want get the Total of all requestitems those who having same RequestID.
RequestID Total
331 11.177

Can you help to write query for the situation?

Thanks.
Cadavre
Cadavre
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2914 Visits: 8448
p.ravirao (7/14/2010)
Hi,
I want write one more select query like below:


I've actually already answered this in the thread you started on it, go check ;-)


Forever trying to learn

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/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


Craig Wilkinson - Software Engineer
LinkedIn
Lowell
Lowell
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18209 Visits: 39425
skcadavre (7/14/2010)
Lowell (7/14/2010)
turning the related rows into a comma delimited list is what you are after right?
the easiest way is to use a neat trick using FOR XML.
here's a nice example I keep handy in my snippets that i think is very intuitive.


Cool, haven't come across this before. Much easier than the CTE I went with.


now that's funny, we both like each others examples.... i just added your example into my snippets so i have more than one way to skin that cat. Thanks!

Lowell

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

p.ravirao
p.ravirao
SSC Rookie
SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)

Group: General Forum Members
Points: 34 Visits: 23
Hi,
I have last question, how can i make this query for dynamic data instead of sample static data.

ex: select the rows from the table.

Thanks again..
Lowell
Lowell
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18209 Visits: 39425
dynamic data ?
did you try either of the solutions we gave you?
both work for an unlimited number of items...it's just string concatination.

if that's not it, explain in detail what you are looking for, and give us the table structures and data so we can copy/paste into SSMS.

Lowell

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

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