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*

• SQLRNNR

  SSC Guru

  Points: 281334

  More actions

  February 7, 2012 at 11:02 am

  #1443989

  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

  February 7, 2012 at 11:06 am

  #1443993

  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: 281334

  More actions

  February 7, 2012 at 11:09 am

  #1443994

  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

  February 7, 2012 at 2:18 pm

  #1444134

  Thanks to all,

  this query can execute with -while statement-?

  ____________________________________________________________________________
  Rafo*

• SQLRNNR

  SSC Guru

  Points: 281334

  More actions

  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: 1004356

  More actions

  February 7, 2012 at 4:16 pm

  #1444212

  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)
  

• xRafo

  SSChasing Mays

  Points: 623

  More actions

  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: 1004504

  More actions

  February 7, 2012 at 4:21 pm

  #1444215

  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: 281334

  More actions

  February 7, 2012 at 4:22 pm

  #1444216

  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

  February 7, 2012 at 4:23 pm

  #1444218

  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

  February 8, 2012 at 7:29 am

  #1444509

  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

  February 8, 2012 at 7:47 am

  #1444522

  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

  February 8, 2012 at 8:39 am

  #1444565

  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

  February 8, 2012 at 8:49 am

  #1444577

  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/

  ---