February 7, 2012 at 10:37 am
Hi All,
i have this data:
DECLARE @data table (n INT)
INSERT @data
SELECT 1 UNION ALL
SELECT 10 UNION ALL
SELECT 11 UNION ALL
SELECT 12 UNION ALL
SELECT 13 UNION ALL
SELECT 14 UNION ALL
SELECT 15 UNION ALL
SELECT 16 UNION ALL
SELECT 17 UNION ALL
SELECT 18 UNION ALL
SELECT 19 UNION ALL
SELECT 2 UNION ALL
SELECT 20 UNION ALL
SELECT 21 UNION ALL
SELECT 22 UNION ALL
SELECT 23 UNION ALL
SELECT 24 UNION ALL
SELECT 25 UNION ALL
SELECT 26 UNION ALL
SELECT 27 UNION ALL
SELECT 28 UNION ALL
SELECT 29 UNION ALL
SELECT 3 UNION ALL
SELECT 30 UNION ALL
SELECT 31 UNION ALL
SELECT 32 UNION ALL
SELECT 33 UNION ALL
SELECT 34 UNION ALL
SELECT 35 UNION ALL
SELECT 36 UNION ALL
SELECT 37 UNION ALL
SELECT 38 UNION ALL
SELECT 39 UNION ALL
SELECT 4 UNION ALL
SELECT 40 UNION ALL
SELECT 41 UNION ALL
SELECT 42 UNION ALL
SELECT 43 UNION ALL
SELECT 44 UNION ALL
SELECT 5 UNION ALL
SELECT 6 UNION ALL
SELECT 7 UNION ALL
SELECT 8 UNION ALL
SELECT 9
DECLARE @cols NVARCHAR(max)
SELECT @cols = STUFF(
( SELECT DISTINCT TOP 100 PERCENT '],''0'' [' + RTRIM(CPA.n)
FROM @data AS CPA
ORDER BY '],''0'' [' + RTRIM(CPA.n)
FOR XML PATH('')), 1, 2, '') + ']'
SELECT @cols
I need the same structure as my result is in order, example: '0' [1],'0' [2],'0' [3],'0' [4],'0' [5] ..etc.
Any help please?
Thanks!.
____________________________________________________________________________
Rafo*
February 7, 2012 at 11:02 am
Try this
DECLARE @data table (n varchar(2))
INSERT @data
SELECT 1 UNION ALL
SELECT 10 UNION ALL
SELECT 11 UNION ALL
SELECT 12 UNION ALL
SELECT 13 UNION ALL
SELECT 14 UNION ALL
SELECT 15 UNION ALL
SELECT 16 UNION ALL
SELECT 17 UNION ALL
SELECT 18 UNION ALL
SELECT 19 UNION ALL
SELECT 2 UNION ALL
SELECT 20 UNION ALL
SELECT 21 UNION ALL
SELECT 22 UNION ALL
SELECT 23 UNION ALL
SELECT 24 UNION ALL
SELECT 25 UNION ALL
SELECT 26 UNION ALL
SELECT 27 UNION ALL
SELECT 28 UNION ALL
SELECT 29 UNION ALL
SELECT 3 UNION ALL
SELECT 30 UNION ALL
SELECT 31 UNION ALL
SELECT 32 UNION ALL
SELECT 33 UNION ALL
SELECT 34 UNION ALL
SELECT 35 UNION ALL
SELECT 36 UNION ALL
SELECT 37 UNION ALL
SELECT 38 UNION ALL
SELECT 39 UNION ALL
SELECT 4 UNION ALL
SELECT 40 UNION ALL
SELECT 41 UNION ALL
SELECT 42 UNION ALL
SELECT 43 UNION ALL
SELECT 44 UNION ALL
SELECT 5 UNION ALL
SELECT 6 UNION ALL
SELECT 7 UNION ALL
SELECT 8 UNION ALL
SELECT 9
DECLARE @cols NVARCHAR(max)
UPDATE @data
SET n = RIGHT('0'+n,2)
SELECT * FROM @data
SELECT @cols = STUFF(
( SELECT DISTINCT TOP 100 PERCENT '],''0'' [' + RTRIM(RIGHT('0' + CPA.n,2))
FROM @data AS CPA
ORDER BY '],''0'' [' + RTRIM(RIGHT('0' + CPA.n,2))
FOR XML PATH('')), 1, 2, '') + ']'
SELECT @cols
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 7, 2012 at 11:06 am
Hope that I undestood what you need:
DECLARE @data table (n INT)
INSERT @data
SELECT 1 UNION ALL
SELECT 10 UNION ALL
SELECT 11 UNION ALL
SELECT 12 UNION ALL
SELECT 13 UNION ALL
SELECT 14 UNION ALL
SELECT 15 UNION ALL
SELECT 16 UNION ALL
SELECT 17 UNION ALL
SELECT 18 UNION ALL
SELECT 19 UNION ALL
SELECT 2 UNION ALL
SELECT 20 UNION ALL
SELECT 21 UNION ALL
SELECT 22 UNION ALL
SELECT 23 UNION ALL
SELECT 24 UNION ALL
SELECT 25 UNION ALL
SELECT 26 UNION ALL
SELECT 27 UNION ALL
SELECT 28 UNION ALL
SELECT 29 UNION ALL
SELECT 3 UNION ALL
SELECT 30 UNION ALL
SELECT 31 UNION ALL
SELECT 32 UNION ALL
SELECT 33 UNION ALL
SELECT 34 UNION ALL
SELECT 35 UNION ALL
SELECT 36 UNION ALL
SELECT 37 UNION ALL
SELECT 38 UNION ALL
SELECT 39 UNION ALL
SELECT 4 UNION ALL
SELECT 40 UNION ALL
SELECT 41 UNION ALL
SELECT 42 UNION ALL
SELECT 43 UNION ALL
SELECT 44 UNION ALL
SELECT 5 UNION ALL
SELECT 6 UNION ALL
SELECT 7 UNION ALL
SELECT 8 UNION ALL
SELECT 9
DECLARE @cols NVARCHAR(max)
SELECT @cols = STUFF(
( SELECT '],''0'' [' + RTRIM(CPA.n)
FROM @data AS CPA
ORDER BY CPA.n
FOR XML PATH('')), 1, 2, '') + ']'
SELECT @cols
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 7, 2012 at 11:09 am
Adi Cohn-120898 (2/7/2012)
Hope that I undestood what you need:
DECLARE @data table (n INT)
INSERT @data
SELECT 1 UNION ALL
SELECT 10 UNION ALL
SELECT 11 UNION ALL
SELECT 12 UNION ALL
SELECT 13 UNION ALL
SELECT 14 UNION ALL
SELECT 15 UNION ALL
SELECT 16 UNION ALL
SELECT 17 UNION ALL
SELECT 18 UNION ALL
SELECT 19 UNION ALL
SELECT 2 UNION ALL
SELECT 20 UNION ALL
SELECT 21 UNION ALL
SELECT 22 UNION ALL
SELECT 23 UNION ALL
SELECT 24 UNION ALL
SELECT 25 UNION ALL
SELECT 26 UNION ALL
SELECT 27 UNION ALL
SELECT 28 UNION ALL
SELECT 29 UNION ALL
SELECT 3 UNION ALL
SELECT 30 UNION ALL
SELECT 31 UNION ALL
SELECT 32 UNION ALL
SELECT 33 UNION ALL
SELECT 34 UNION ALL
SELECT 35 UNION ALL
SELECT 36 UNION ALL
SELECT 37 UNION ALL
SELECT 38 UNION ALL
SELECT 39 UNION ALL
SELECT 4 UNION ALL
SELECT 40 UNION ALL
SELECT 41 UNION ALL
SELECT 42 UNION ALL
SELECT 43 UNION ALL
SELECT 44 UNION ALL
SELECT 5 UNION ALL
SELECT 6 UNION ALL
SELECT 7 UNION ALL
SELECT 8 UNION ALL
SELECT 9
DECLARE @cols NVARCHAR(max)
SELECT @cols = STUFF(
( SELECT '],''0'' [' + RTRIM(CPA.n)
FROM @data AS CPA
ORDER BY CPA.n
FOR XML PATH('')), 1, 2, '') + ']'
SELECT @cols
Adi
Simplest solution - looks good to me.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 7, 2012 at 2:18 pm
Thanks to all,
this query can execute with -while statement-?
____________________________________________________________________________
Rafo*
February 7, 2012 at 2:34 pm
Are you trying to throw this into a loop?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 7, 2012 at 4:16 pm
xRafo (2/7/2012)
Thanks to all,this query can execute with -while statement-?
Yes... it can. Why would you want to use slower code for this, though?
--Jeff Moden
Change is inevitable... Change for the better is not.
February 7, 2012 at 4:20 pm
what do you mean, with the clause while gonna be slow?
____________________________________________________________________________
Rafo*
February 7, 2012 at 4:21 pm
While loops are row-by-row processing and they are generally slower and less efficient than a comparable set-based solution.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 7, 2012 at 4:22 pm
Jeff Moden (2/7/2012)
xRafo (2/7/2012)
Thanks to all,this query can execute with -while statement-?
Yes... it can. Why would you want to use slower code for this, though?
Taking this set based query and converting it into a while loop will be slower.
If you don't mean "While loop" then please provide a code sample of what you are trying to do.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 7, 2012 at 4:23 pm
with this query i have the same result:
DECLARE @number int, @C INT
SET @number =9
SET @C =0
DECLARE @colstotal VARCHAR(MAX)
SET @colstotal=''
WHILE @number >= @C
BEGIN
SET @colstotal=@colstotal+'''0''['+CONVERT(VARCHAR(2),@c)+'],'
SET @C=@c+1
END
SET @colstotal=SUBSTRING(@colstotal,1,LEN(@colstotal)-1)
print @colstotal
how do I select the top into a variable? (@number)
(i tried but I get an Error :Incorrect syntax near '@number'.
SELECT @cols = STUFF(
( SELECT DISTINCT TOP @number PERCENT '],''0'' [' + RTRIM(RIGHT('0' + CPA.n,2))
FROM @data AS CPA
ORDER BY '],''0'' [' + RTRIM(RIGHT('0' + CPA.n,2))
FOR XML PATH('')), 1, 2, '') + ']'
SELECT @cols
Many thanks.
____________________________________________________________________________
Rafo*
February 8, 2012 at 7:29 am
I tried with this:
SELECT @cols = STUFF(
( SELECT DISTINCT TOP (@number) PERCENT '],''0'' [' + CPA.n
FROM @data AS CPA
ORDER BY CPA.n
FOR XML PATH('')), 1, 2, '') + ']'
SELECT @cols
and the result it's: When you specify SELECT DISTINCT, ORDER BY items must appear in the list.
Any recommendation please?
____________________________________________________________________________
Rafo*
February 8, 2012 at 7:47 am
I'd have probably done it like this
DECLARE @cols NVARCHAR(max) DECLARE @number INT
SET @number = 9
SELECT @cols = STUFF((SELECT '],''0'' [' + RTRIM(CPA.n)
FROM (SELECT n, ROW_NUMBER() OVER(ORDER BY n) AS rn
FROM @data) CPA
WHERE rn <= @number
ORDER BY rn
FOR XML PATH('')), 1, 2, '') + ']'
SELECT @cols
February 8, 2012 at 8:39 am
Thanks,
it work of course, but have a detail..
when in my table exists 2 values equals i just need 1
you see:
DECLARE @number int, @C INT
SET @number =9
SET @C =1
DECLARE @cols VARCHAR(MAX)
SET @cols=''
declare @data table (n int)
INSERT @data
SELECT 3 UNION ALL
SELECT 37 UNION ALL
SELECT 27 UNION ALL
SELECT 2 UNION ALL
SELECT 14 UNION ALL
SELECT 2 UNION ALL
SELECT 6 UNION ALL
SELECT 41 UNION ALL
SELECT 9 UNION ALL
SELECT 8 UNION ALL
SELECT 5 UNION ALL
SELECT 7 UNION ALL
SELECT 44 UNION ALL
SELECT 1 UNION ALL
SELECT 13 UNION ALL
SELECT 4
SELECT @cols = STUFF((SELECT '],''0'' [' + RTRIM(CPA.n)
FROM (SELECT n, ROW_NUMBER() OVER(ORDER BY n) AS rn
FROM @data) CPA
WHERE rn <= @number
ORDER BY rn
FOR XML PATH('')), 1, 2, '') + ']'
SELECT @cols
--result: '0' [1],'0' [b]2[/b],'0' [b]2[/b],'0' [3],'0' [4],'0' [5],'0' [6],'0' [7],'0' [8]
--and the value 9 is not selected..
____________________________________________________________________________
Rafo*
February 8, 2012 at 8:49 am
SELECT @cols = STUFF((SELECT '],''0'' [' + RTRIM(CPA.n)
FROM (SELECT n, ROW_NUMBER() OVER(ORDER BY n) AS rn,
ROW_NUMBER() OVER(PARTITION BY n ORDER BY n) AS dup
FROM @data) CPA
WHERE rn <= @number AND dup = 1
ORDER BY rn
FOR XML PATH('')), 1, 2, '') + ']'
SELECT @cols
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply