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 6:01 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 8:47 AM
Points: 2,422, Visits: 7,443
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.



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 #952232
Posted Wednesday, July 14, 2010 6:03 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, October 15, 2010 4:35 AM
Points: 18, Visits: 23
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
Post #952236
Posted Wednesday, July 14, 2010 6:09 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 8:47 AM
Points: 2,422, Visits: 7,443
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




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 #952238
Posted Wednesday, July 14, 2010 6:12 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, October 15, 2010 4:35 AM
Points: 18, Visits: 23
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
Post #952244
Posted Wednesday, July 14, 2010 6:13 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, October 15, 2010 4:35 AM
Points: 18, Visits: 23
Yes. I am very new to write sql queries.

I will try get understand your queries..Thanks for your time.

Post #952247
Posted Wednesday, July 14, 2010 6:13 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 8:47 AM
Points: 2,422, Visits: 7,443
p.ravirao (7/14/2010)
Thanks!

When i am modifying your query like below. I am getting zero rows.


There's no data in the table variable that you've created there, so it will return zero rows.



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 #952248
Posted Wednesday, July 14, 2010 6:17 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, October 15, 2010 4:35 AM
Points: 18, Visits: 23
Thanks!. I got it.I got the results .
Post #952252
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse