query

• sachince61

  Old Hand

  Points: 329

  More actions

  May 11, 2012 at 12:17 am

  #273886

  I have a table with two column

  out put date is like this...

  mnemonicId customerID

  1402 140201

  1402 140202

  1402 140203

  now i want out put like this

  mnemonicId customerID xyz

  1402 140201 140202

  1402 140201 140203

  1402 140202 140203

  so please help .....

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  May 11, 2012 at 12:30 am

  #1486108

  What is the logic here?

  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

• sachince61

  Old Hand

  Points: 329

  More actions

  May 11, 2012 at 12:51 am

  #1486115

  Sorry i havent any logic ....

• Ant-Green

  SSC Guru

  Points: 113507

  More actions

  May 11, 2012 at 1:02 am

  #1486116

  this will do the job with the sample data provided, but if you dont know the logic then we can only guess at what you want and how it should be done.

  declare @table table (mnemonicid int, customerid int)

  insert into @table values (1402,140201),(1402,140201),(1402,140202)

  ;with cte as

  (

  select

  mnemonicid,

  customerid,

  ROW_NUMBER() OVER(PARTITION BY customerid ORDER BY customerid) as RowNum

  from

  @table

  )

  select

  mnemonicid,

  customerid,

  customerid + rownum AS xyz

  from

  cte

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  May 11, 2012 at 1:06 am

  #1486117

  sachince61 (5/11/2012)

  ---

  Sorry i havent any logic ....

  Really? So any two customerIDs on the same line, like this?

  mnemonicId customerID xyz

  1402 140201 140202

  1402 140203 140201

  1402 140202 140201

  ?

  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

• vinu512

  SSCoach

  Points: 15729

  More actions

  May 11, 2012 at 1:18 am

  #1486122

  sachince61 (5/11/2012)

  ---

  I have a table with two column

  out put date is like this...

  mnemonicId customerID

  1402 140201

  1402 140202

  1402 140203

  now i want out put like this

  mnemonicId customerID xyz

  1402 140201 140202

  1402 140201 140203

  1402 140202 140203

  so please help .....

  If there is no logic then why do you want to do it??...What is the business requirement behind this??

  Vinu Vijayan

  For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉

• vinu512

  SSCoach

  Points: 15729

  More actions

  May 11, 2012 at 1:31 am

  #1486128

  anthony.green (5/11/2012)

  ---

  this will do the job with the sample data provided, but if you dont know the logic then we can only guess at what you want and how it should be done.

  declare @table table (mnemonicid int, customerid int)

  insert into @table values (1402,140201),(1402,140201),(1402,140202)

  ;with cte as

  (

  select

  mnemonicid,

  customerid,

  ROW_NUMBER() OVER(PARTITION BY customerid ORDER BY customerid) as RowNum

  from

  @table

  )

  select

  mnemonicid,

  customerid,

  customerid + rownum AS xyz

  from

  cte

  I tried the same thing Anthony.

  But then I checked out the sample data and the required result set posted by the OP and saw this difference in the last row:

  mnemonicId customerID

  1402 140201

  1402 140202

  1402 140203

  now i want out put like this

  mnemonicId customerID xyz

  1402 140201 140202

  1402 140201 140203

  1402 140202 140203

  Vinu Vijayan

  For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉

• Ant-Green

  SSC Guru

  Points: 113507

  More actions

  May 11, 2012 at 1:37 am

  #1486130

  vinu512 (5/11/2012)

  ---

  anthony.green (5/11/2012)

  ---

  this will do the job with the sample data provided, but if you dont know the logic then we can only guess at what you want and how it should be done.

  declare @table table (mnemonicid int, customerid int)

  insert into @table values (1402,140201),(1402,140201),(1402,140202)

  ;with cte as

  (

  select

  mnemonicid,

  customerid,

  ROW_NUMBER() OVER(PARTITION BY customerid ORDER BY customerid) as RowNum

  from

  @table

  )

  select

  mnemonicid,

  customerid,

  customerid + rownum AS xyz

  from

  cte

  I tried the same thing Anthony.

  But then I checked out the sample data and the required result set posted by the OP and saw this difference in the last row:

  mnemonicId customerID

  1402 140201

  1402 140202

  1402 140203

  now i want out put like this

  mnemonicId customerID xyz

  1402 140201 140202

  1402 140201 140203

  1402 140202 140203

  Excellent spot Vinu, I totally missed that, anyway if the OP doesnt know the logic or the business requirements to do the task then we can only speculate. One thing I would ask is that the OP follow the best practise links in our signatures on the next post with the logic so that we can help them better.

• sachince61

  Old Hand

  Points: 329

  More actions

  May 11, 2012 at 2:46 am

  #1486161

  Thanks for reply....