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


Select query


Select query

Author
Message
Cadavre
Cadavre
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14669 Visits: 8510
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.


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 Journeyman
SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)

Group: General Forum Members
Points: 92 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
Cadavre
Cadavre
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14669 Visits: 8510
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




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 Journeyman
SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)

Group: General Forum Members
Points: 92 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
p.ravirao
p.ravirao
SSC Journeyman
SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)

Group: General Forum Members
Points: 92 Visits: 23
Yes. I am very new to write sql queries.

I will try get understand your queries..Thanks for your time.
Cadavre
Cadavre
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14669 Visits: 8510
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.


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 Journeyman
SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)

Group: General Forum Members
Points: 92 Visits: 23
Thanks!. I got it.I got the results .
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