query help

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » query help

query help

Rate Topic

Display Mode

Topic Options

Author

Message

sqlnaive

Posted Thursday, January 17, 2013 2:30 AM

SSCrazy

Group: General Forum Members
Last Login: Today @ 4:30 AM
Points: 2,657, Visits: 1,662

In a table, I have value like this:

1A
2A

I want data like this:
1C
1D
2C
2D

that is for each value of A in table, i want two records with C and D.

Post #1408240

mister.magoo

Posted Thursday, January 17, 2013 3:10 AM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 12:46 PM
Points: 1,290, Visits: 3,860

sqlnaive (1/17/2013)

In a table, I have value like this:

1A
2A

I want data like this:
1C
1D
2C
2D

that is for each value of A in table, i want two records with C and D.

SELECT newColumn
FROM yourTable as yt
CROSS APPLY (
  SELECT LEFT(yt.yourColumn,1)+'C'
  UNION ALL
  SELECT LEFT(yt.yourColumn,1)+'D'
  ) AS new(newColumn)

Without table definitions and sample data, this is just a sample showing how CROSS APPLY can be used to expand 1 row to many...

MM

Post #1408259

sqlnaive

Posted Thursday, January 17, 2013 4:51 AM

SSCrazy

Group: General Forum Members
Last Login: Today @ 4:30 AM
Points: 2,657, Visits: 1,662

Cool. Thanks. I was trying this by creating temp table and then making CROSS JOIN. But definitely it's better. Smile

Post #1408334

dwain.c

Posted Thursday, January 17, 2013 5:32 PM

SSCrazy

Group: General Forum Members
Last Login: Today @ 3:20 AM
Points: 2,341, Visits: 3,175

This should also work:

SELECT newColumn
FROM yourTable as yt
CROSS APPLY (
  VALUES (LEFT(yt.yourColumn,1)+'C')
    ,(LEFT(yt.yourColumn,1)+'D') new (newColumn)

No loops! No CURSORs! No RBAR! Hoo-uh!

INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Are you too recursively challenged?
Splitting strings based on patterns can be fast!

Post #1408681

« Prev Topic | Next Topic »

Permissions