Help with a query.

Question

Post reply

Help with a query.

xRafo

SSChasing Mays

Points: 623
More actions
February 7, 2012 at 10:37 am

#391497

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*

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281324 More actions · Answer 1

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

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 2

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/

SQLRNNR SSC Guru Points: 281324 More actions · Answer 3

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

xRafo SSChasing Mays Points: 623 More actions · Answer 4

xRafo

SSChasing Mays

Points: 623

February 7, 2012 at 2:18 pm

#1444134

Thanks to all,

this query can execute with -while statement-?

____________________________________________________________________________
Rafo*

SQLRNNR SSC Guru Points: 281324 More actions · Answer 5

SQLRNNR

SSC Guru

Points: 281324

February 7, 2012 at 2:34 pm

#1444143

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

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 6

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

xRafo SSChasing Mays Points: 623 More actions · Answer 7

xRafo

SSChasing Mays

Points: 623

February 7, 2012 at 4:20 pm

#1444213

what do you mean, with the clause while gonna be slow?

____________________________________________________________________________
Rafo*

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 8

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQLRNNR SSC Guru Points: 281324 More actions · Answer 9

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

xRafo SSChasing Mays Points: 623 More actions · Answer 10

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*

xRafo SSChasing Mays Points: 623 More actions · Answer 11

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*

Cadavre SSC-Forever Points: 41690 More actions · Answer 12

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

Forever trying to learn
My blog - http://www.cadavre.co.uk/
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/

xRafo SSChasing Mays Points: 623 More actions · Answer 13

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*

Cadavre SSC-Forever Points: 41690 More actions · Answer 14

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

Forever trying to learn
My blog - http://www.cadavre.co.uk/
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/