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 12»»

Select query Expand / Collapse
Author
Message
Posted Wednesday, July 14, 2010 5:15 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, October 15, 2010 4:35 AM
Points: 18, 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?
Post #952187
Posted Wednesday, July 14, 2010 5:37 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 5:38 AM
Points: 2,379, Visits: 7,579
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
*/




Not a DBA, just 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


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #952200
Posted Wednesday, July 14, 2010 5:37 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 6:44 AM
Points: 12,905, Visits: 32,158
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #952201
Posted Wednesday, July 14, 2010 5:40 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 5:38 AM
Points: 2,379, Visits: 7,579
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.



Not a DBA, just 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


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #952206
Posted Wednesday, July 14, 2010 5:45 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, October 15, 2010 4:35 AM
Points: 18, Visits: 23
Thanks a lot!! ..much appriciated for your help..its perfect..
Post #952213
Posted Wednesday, July 14, 2010 5:46 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, October 15, 2010 4:35 AM
Points: 18, 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.
Post #952217
Posted Wednesday, July 14, 2010 5:48 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 5:38 AM
Points: 2,379, Visits: 7,579
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



Not a DBA, just 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


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #952220
Posted Wednesday, July 14, 2010 5:50 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 6:44 AM
Points: 12,905, Visits: 32,158
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #952222
Posted Wednesday, July 14, 2010 5:56 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, October 15, 2010 4:35 AM
Points: 18, 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..
Post #952228
Posted Wednesday, July 14, 2010 5:59 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 6:44 AM
Points: 12,905, Visits: 32,158
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #952229
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse