SQL Server query that add a column in a Select Query

Question

I have two tables CX_SOLICITUD_SERVICIO and CX_SOLICITUD_SALA, and I need a SQL Server query that add a column if NU_CONS_SOSA has more than one CD_CODI_SER_SOSE.

Table 1: CX_SOLICITUD_SERVICIO

NU_CONS_SOSA_SOSE   CD_CODI_SER_SOSE
------------------------------------
    777                    i684000
    777                    i705210
    777                    i595101
    778                    i684000
    778                    i549201
    756                    i132300
    763                    i132300
    758                    i132300
    761                    i132300
    761                    i132301

Table 2: CX_SOLICITUD_SALA

NU_CONS_SOSA             FE_FECH_SOSA
--------------------------------------------
        777         2017-03-01 10:14:00.000
        778         2017-03-04 11:14:00.000
        756         2017-02-01 12:14:00.000
        763         2017-01-15 14:14:00.000
        758         2017-02-27 09:14:00.000
        761         2017-03-01 10:14:00.000

Expected result:

NU_CONS_SOSA    CD_CODI_SER_SOSE_1  CD_CODI_SER_SOSE_2    CD_CODI_SER_SOSE_3
       777             i684000           i684000                i595101
       778             i684000           i549201                null
       756             i132300           null                   null
       763             i132300           null                   null
       758             i132300           null                   null
       761             i132300           i132301                null

I normally try the following query but it doesn't work:

SELECT 
    NU_CONS_SOSA_SOSE, CD_CODI_SER_SOSE
FROM 
    CX_SOLICITUD_SERVICIO 
INNER JOIN 
    CX_SOLICITUD_SALA ON NU_CONS_SOSA = NU_CONS_SOSA_SOSE

Is there a max number of CD_CODI_SER_SOSE for each NU_CONS_SOSA ? — McNets, Mar 13 '17 at 16:46
Yep - you want to PIVOT; http://stackoverflow.com/questions/15931607/convert-rows-to-columns-using-pivot-in-sql-server — David Manheim, Mar 13 '17 at 16:48
Possible duplicate of [Convert Rows to columns using 'Pivot' in SQL Server](http://stackoverflow.com/questions/15931607/convert-rows-to-columns-using-pivot-in-sql-server) — David Manheim, Mar 13 '17 at 16:49

score 0 · Accepted Answer · answered Mar 13 '17 at 16:53

Using dynamic sql to generate code to pivot() an unknown maximum number of CD_CODI_SER_SOSE:

declare @cols nvarchar(max);
declare @sql  nvarchar(max);

  select @cols = stuff((
    select distinct 
      ',' + quotename('CD_CODI_SER_SOSE_'
          +convert(nvarchar(10),row_number() over (
              partition by NU_CONS_SOSA_SOSE 
              order by     CD_CODI_SER_SOSE 
          ))
          )
      from CX_SOLICITUD_SERVICIO 
      for xml path (''), type).value('.','nvarchar(max)')
    ,1,1,'');

select @sql = '
 select NU_CONS_SOSA_SOSE, ' + @cols + '
  from  (
    select 
        NU_CONS_SOSA_SOSE
      , CD_CODI_SER_SOSE
      , rn=''CD_CODI_SER_SOSE_''+convert(nvarchar(10),row_number() over (
          partition by NU_CONS_SOSA_SOSE 
          order by     CD_CODI_SER_SOSE 
          ))
      from CX_SOLICITUD_SERVICIO
      ) as a
 pivot (max([CD_CODI_SER_SOSE]) for [rn] in (' + @cols + ') ) p';
 select @sql as CodeGenerated;
 exec sp_executesql @sql;

rextester demo: http://rextester.com/OFWRR74959

code generated:

 select NU_CONS_SOSA_SOSE, [CD_CODI_SER_SOSE_1],[CD_CODI_SER_SOSE_2],[CD_CODI_SER_SOSE_3]
  from  (
    select 
        NU_CONS_SOSA_SOSE
      , CD_CODI_SER_SOSE
      , rn='CD_CODI_SER_SOSE_'+convert(nvarchar(10),row_number() over (
          partition by NU_CONS_SOSA_SOSE 
          order by     CD_CODI_SER_SOSE 
          ))
      from CX_SOLICITUD_SERVICIO
      ) as a
 pivot (max([CD_CODI_SER_SOSE]) for [rn] in ([CD_CODI_SER_SOSE_1],[CD_CODI_SER_SOSE_2],[CD_CODI_SER_SOSE_3]) ) p

results:

+-------------------+--------------------+--------------------+--------------------+
| NU_CONS_SOSA_SOSE | CD_CODI_SER_SOSE_1 | CD_CODI_SER_SOSE_2 | CD_CODI_SER_SOSE_3 |
+-------------------+--------------------+--------------------+--------------------+
|               756 | i132300            | NULL               | NULL               |
|               758 | i132300            | NULL               | NULL               |
|               761 | i132300            | i132301            | NULL               |
|               763 | i132300            | NULL               | NULL               |
|               777 | i595101            | i684000            | i705210            |
|               778 | i549201            | i684000            | NULL               |
+-------------------+--------------------+--------------------+--------------------+

SQL Server query that add a column in a Select Query

1 Answers1