Very Computer

I need to write a single T-SQL query (if possible) that will create
the desired output listed below given the example data in #test.  The
requirements are to transpose the spouse rows to columns by
combining/joining the spouse rows (type = 'S') from #test with each
corresponding employee row (type = 'E') from #test where the ssn for
the employee row equals the sp_ssn for the spouse row. New columns
should be added to #test that will contain the spouse data. Note that
the initial spouse rows on #test need to be removed from the final
output.

The newly added spouse columns should be prefaced with SP_* to
distinguish them from the employee columns. Is there a way to preface
with SP_* without listing each variable (e.g. not as "SELECT fname AS
SP_fname, lname AS SP_lname," etc.) since there are many variables
which were removed for this example that I do not want to explicitly
list.

Can this be done using a single T-SQL query or should it be broken up
into several queries? Any assistance and/or code on how to do this
would be much appreciated.  Please respond to this newsgroup, not via
email address.
Thanks, Mike

CREATE TABLE #test
(
ssn CHAR(1),
sp_ssn CHAR(9),
fname CHAR(15),
lname CHAR(15),
amt NUMERIC(7),
type CHAR(1),
CONSTRAINT pk_test PRIMARY KEY (ssn)
)
INSERT #test VALUES('1',' ','mark','smith',100000,'E')
INSERT #test VALUES('2','3','mike','sharp',100000,'E')
INSERT #test VALUES('3','2','beth','sharp',1000,'S')
INSERT #test VALUES('4','5','john','doe',500000,'E')
INSERT #test VALUES('5','4','jane','doe',5000,'S')
INSERT #test VALUES('6',' ','chris','good',50000,'E')

select * from #test

input:

ssn sp    fname   lname   amt      type
    _ssn
--- ---   ------- ------- -------  -----
1         mark    smith   100000   E
2   3     mike    sharp   100000   E
3   2     beth    sharp   1000     S
4   5     john    doe     500000   E
5   4     jane    doe     5000     S
6         chris   good    50000    E

desired output:

ssn sp    fname lname amt    type SP    SP_sp SP_    SP_   SP_  SP_
    _ssn                          _ssn  _SSN  fname  lname amt  type
--- ----- ----- ----- ------ ---- ----- ----  ------ ----  ---- ----
1         mark  smith 100000 E
2   3     mike  sharp 100000 E    3     2     beth   sharp 1000 S
4   5     john  doe   500000 E    5     4     jane   doe   5000 S
6         chris good  50000  E

One way to do it:

select t.ssn
        , t.sp_ssn
        , t.fname
        , t.lname
        , t.amt
        , t.type
        , ISNULL((select ssn from #test where sp_ssn =
t.ssn and type = 'S'), '') SP_SSN
        , ssn SP_SP_SSN
        , ISNULL((select fname from #test where type = 'S'
and t.ssn = sp_ssn), '') SP_FNAME
        , ISNULL((select lname from #test where type = 'S'
and t.ssn = sp_ssn), '') SP_LNAME
        , ISNULL((select type from #test where type = 'S'
and t.ssn = sp_ssn), '') SP_TYPE
from #test t
where type = 'E'

Quote:>-----Original Message-----
>I need to write a single T-SQL query (if possible) that
will create
>the desired output listed below given the example data in
#test.  The
>requirements are to transpose the spouse rows to columns
by
>combining/joining the spouse rows (type = 'S') from #test
with each
>corresponding employee row (type = 'E') from #test where
the ssn for
>the employee row equals the sp_ssn for the spouse row.
New columns
>should be added to #test that will contain the spouse
data. Note that
>the initial spouse rows on #test need to be removed from
the final
>output.

>The newly added spouse columns should be prefaced with
SP_* to
>distinguish them from the employee columns. Is there a
way to preface
>with SP_* without listing each variable (e.g. not
as "SELECT fname AS
>SP_fname, lname AS SP_lname," etc.) since there are many
variables
>which were removed for this example that I do not want to
explicitly
>list.

>Can this be done using a single T-SQL query or should it
be broken up
>into several queries? Any assistance and/or code on how
to do this
>would be much appreciated.  Please respond to this
newsgroup, not via
>email address.
>Thanks, Mike

>CREATE TABLE #test
>(
>ssn CHAR(1),
>sp_ssn CHAR(9),
>fname CHAR(15),
>lname CHAR(15),
>amt NUMERIC(7),
>type CHAR(1),
>CONSTRAINT pk_test PRIMARY KEY (ssn)
>)
>INSERT #test VALUES('1',' ','mark','smith',100000,'E')
>INSERT #test VALUES('2','3','mike','sharp',100000,'E')
>INSERT #test VALUES('3','2','beth','sharp',1000,'S')
>INSERT #test VALUES('4','5','john','doe',500000,'E')
>INSERT #test VALUES('5','4','jane','doe',5000,'S')
>INSERT #test VALUES('6',' ','chris','good',50000,'E')

>select * from #test

>input:

>ssn sp    fname   lname   amt      type
>    _ssn
>--- ---   ------- ------- -------  -----
>1         mark    smith   100000   E
>2   3     mike    sharp   100000   E
>3   2     beth    sharp   1000     S
>4   5     john    doe     500000   E
>5   4     jane    doe     5000     S
>6         chris   good    50000    E

>desired output:

>ssn sp    fname lname amt    type SP    SP_sp SP_    
SP_   SP_  SP_
>    _ssn                          _ssn  _SSN  fname  
lname amt  type
>--- ----- ----- ----- ------ ---- ----- ----  ------ ----
  ---- ----
>1         mark  smith 100000 E
>2   3     mike  sharp 100000 E    3     2     beth  
sharp 1000 S
>4   5     john  doe   500000 E    5     4     jane  
doe   5000 S
>6         chris good  50000  E
>.

This looks like it would work. Thanks! To take this a step further,
say that there might be additional columns on #test since this table
and the columns on it are built dynamically by a VB application - it's
even possible that fname, lname, and amt columns will not be present
(ssn, sp_ssn and type will always be present). What changes would be
needed to transpose all available columns on #test, which will not be
known until runtime, without explicitly listing them?  Or should every
possible column be listed in the select clause and a check for the
existence of the column performed (perhaps using a CASE statement)
before returning in the final output? How would this be accomplished?

Thanks, Mike

<<>>

Quote:> This looks like it would work. Thanks! To take this a step further,
> say that there might be additional columns on #test since this table
> and the columns on it are built dynamically by a VB application - it's
> even possible that fname, lname, and amt columns will not be present
> (ssn, sp_ssn and type will always be present). What changes would be
> needed to transpose all available columns on #test, which will not be
> known until runtime, without explicitly listing them?

Fairly radical changes.
If there are a different number of select to insert columns in a
query, it'll *.

Quote:>  Or should every
> possible column be listed in the select clause and a check for the
> existence of the column performed (perhaps using a CASE statement)
> before returning in the final output? How would this be accomplished?

> Thanks, Mike

You could possibly use a cursor and do the check for each "in code"...
But be warned that iterating through columns is not as easy as in VB.

It's probably easier to dynamically build your sql as you build your
table.
Dunno what you're trying to do but the difficulty in coping with this
would make me think twice about dynamically building tables can have
different columns...
YMMV

HTH