Structure SQL table in a step format

Question

I am currently facing an issue in SQL server, and I probably don't have enough knowledge on how scalable it is in terms of automating certain procedures. So I'd like to know if the following is possible:

I have a table, say:

AR_Code CD_code CO_Code Centre  Cost
   AL     3F2   811      ops    floc
   AL     3D2   812      ops    cell

I would ideally like to get it in the following format, where it can be dynamic enough to shift depending on the number of fields available:

AR_Code CD_code CO_Code Centre  Cost
AL       NULL     NULL    NULL  NULL
AL       3F2      NULL    NULL  NULL
AL       3F2      811     NULL  NULL
AL       3F2      811     ops   NULL
AL       3F2      811     ops   floc
AL       NULL     NULL    NULL  NULL
AL       3D2      NULL    NULL  NULL
AL       3D2      812     NULL  NULL
AL       3D2      812     ops   NULL
AL       3D2      812     ops   cell

it almost creates a step between fields. It is also how some accounting software packages may store data. My current approach is a nightmare, and it involves unions while being very unscalable:

SELECT 
        AR
      ,NULL as [CD code]
      ,NULL as [CO Code]
      ,NULL as [Centre ]
      ,NULL as [Cost   ]
  FROM Table
UNION ALL
SELECT 
        AR
      ,CD code as [CD code]
      ,NULL as [CO Code]
      ,NULL as [Centre ]
      ,NULL as [Cost   ]
  FROM Table
etc...

I'm not sure if anyone has encountered this problem, or has a smart way to approach this - I can worry about the order by later, but my main question would be:

• Is there a more scalable way to approach this?

Thanks.

-R

Answer 1

You can use the APPLY operator to transpose the values into the shape you're looking for. The shape you described requires N rows in the APPLY clause where N is the number of columns involved. Starting with the full row we simply iterate N - 1 times and make a new column null in each iteration:

0. (AR_Code, CD_code, CO_Code, Centre, Cost)
1. (AR_Code, CD_code, CO_Code, Centre, null)
...
4. (AR_Code, null, null, null, null)

With this construction we can simply APPLY the logic to each row in the original table.

select b.*
from (values
    (N'AL', N'3F2', N'811', N'ops', N'floc')
  , (N'AL', N'3D2', N'812', N'ops', N'cell')
) as a (AR_Code, CD_code, CO_Code, Centre, Cost)
cross apply (values
    (a.AR_Code, null, null, null, null)
  , (a.AR_Code, a.CD_code, null, null, null)
  , (a.AR_Code, a.CD_code, a.CO_Code, null, null)
  , (a.AR_Code, a.CD_code, a.CO_Code, a.Centre, null)
  , (a.AR_Code, a.CD_code, a.CO_Code, a.Centre, a.Cost)
) as b (AR_Code, CD_code, CO_Code, Centre, Cost);

This should happen in a single pass and all you're really doing here is changing the cardinality of the original set by multiplying the rows in a by the rows in b.