July 14, 2010 at 5:15 am
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?
July 14, 2010 at 5:37 am
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
*/
July 14, 2010 at 5:37 am
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
July 14, 2010 at 5:40 am
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.
July 14, 2010 at 5:45 am
Thanks a lot!! ..much appriciated for your help..its perfect..
July 14, 2010 at 5:46 am
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.
July 14, 2010 at 5:48 am
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 😉
July 14, 2010 at 5:50 am
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
July 14, 2010 at 5:56 am
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..
July 14, 2010 at 5:59 am
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
July 14, 2010 at 6:01 am
p.ravirao (7/14/2010)
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..
I'm guessing here, but do you mean instead of using the sample data I added into my script?
Change: -
FROM @TABLE
To
FROM YOURTABLENAMEHERE
I think if I were you, I'd try out Lowell's thing as well. Looks much easier to read to me, which makes it easier to maintain.
July 14, 2010 at 6:03 am
Please see my details below in bold text
DECLARE @TABLE AS TABLE(
[RequestID] INT,
[ApprovalName] VARCHAR(35))
INSERT INTO @TABLE([RequestID],[ApprovalName])
--- the below sample data i want query , i wnat use for tables which having data
SELECT 15, 'Manager'
UNION ALL SELECT 15, 'Director'
UNION ALL SELECT 16, 'Manager'
UNION ALL SELECT 16, 'Director'
UNION ALL SELECT 16, 'Procurement Head'
-- the below one is table which is having data
select RequestID,ApproveLevelName from dbo.ts_Status
--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
July 14, 2010 at 6:09 am
OK. Looks to me like you're pretty new to SQL. So, can I suggest that you take some time out to ensure you understand how both examples work?
;WITH cte ( requestid, approvallist, approvelevelname, length )
AS (SELECT requestid,
CAST('' AS VARCHAR(8000)),
CAST('' AS VARCHAR(8000)),
0
FROM dbo.ts_status
GROUP BY requestid
UNION ALL
SELECT p.requestid,
CAST(approvallist + CASE
WHEN length = 0 THEN ''
ELSE ', '
END + p.approvelevelname AS VARCHAR(8000)),
CAST(p.approvelevelname AS VARCHAR(8000)),
length + 1
FROM cte c
INNER JOIN dbo.ts_status p
ON c.requestid = p.requestid
WHERE p.approvelevelname > c.approvelevelname)
SELECT requestid,
approvallist
FROM (SELECT requestid,
approvallist,
Rank() OVER ( PARTITION BY requestid ORDER BY length DESC )
FROM cte) d ( requestid, approvallist, rank )
WHERE rank = 1
July 14, 2010 at 6:12 am
Thanks!
When i am modifying your query like below. I am getting zero rows.
Please can you check my query.
DECLARE @ts_vSignOffStatus AS TABLE(
[RequestID] INT,
[ApproveLevelName] VARCHAR(35))
--Now to the query
;WITH cte ( [RequestID], [Approval List], [ApproveLevelName], length )
AS (SELECT [RequestID],
CAST('' AS VARCHAR(8000)),
CAST('' AS VARCHAR(8000)),
0
FROM @ts_vSignOffStatus
GROUP BY [RequestID]
UNION ALL
SELECT p.[RequestID],
CAST([Approval List] + CASE
WHEN length = 0 THEN ''
ELSE ', '
END + p.[ApproveLevelName] AS VARCHAR(8000)),
CAST(p.[ApproveLevelName] AS VARCHAR(8000)),
length + 1
FROM cte c
INNER JOIN @ts_vSignOffStatus p
ON c.[RequestID] = p.[RequestID]
WHERE p.[ApproveLevelName] > c.[ApproveLevelName])
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
July 14, 2010 at 6:13 am
Yes. I am very new to write sql queries.
I will try get understand your queries..Thanks for your time.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply