SQL - Split columns into rows

[solomon_13000]

When the exchCode is empty the record shouldn't be displayed. However the code below display the records.

SELECT * FROM(
SELECT
   bhCliCode,
   exchCode =
      CASE X.Which
      WHEN 'accSgx' THEN accSgx 
      WHEN 'accHk' THEN accHk
      WHEN 'accIdx' THEN accIdx
      WHEN 'accUs' THEN accUs
      WHEN 'accSet' THEN accSet
      WHEN 'accPhp' THEN accPhp
      END
FROM
   ds_lobclient
   CROSS JOIN (
   SELECT 'accSgx'  
   UNION ALL 
   SELECT 'accHk'
   UNION ALL
   SELECT 'accIdx'
   UNION ALL
   SELECT 'accUs'
   UNION ALL
   SELECT 'accSet'
   UNION ALL
   SELECT 'accPhp'
   ) 
   X (Which) 
   ) t

   where exchCode is not null

How do I solve this problem?. Your help is kindly appreciated.

Thank You.

[BitBlt]

There seems to be nothing wrong with your statement. I put together a tinkertoy version (based on a bunch of assumptions which may or may not be correct), and executed your statement on a SQL2008 instance and it seemed to work fine.

So, it's impossible to help you unless you help us out here.

First off, you need to supply a table definition for ds_lobclient.

Then, you need to supply some sample data (suitably scrubbed to maintain confidentiality if that's an issue).

Then you need to supply an expected result based on your sample data.

Then you need to supply your ACTUAL result (and if necessary, point out how it differs from your expected result).

Until you do that, we have no way of knowing how you got your error or unexpected result.

[solomon_13000]

table: ds_lobclient
fields: id, cliCode, bhCliCode, accSgx, accHk, accIdx, accUs, accSet, accPhp

sample data:
id, cliCode, bhCliCode, accSgx, accHk, accIdx, accUs, accSet, accPhp
1, C100, TC100, 7005, null, null, null, null, null
2, C100, TC100, 7005, 7006, null, null, null, null
3, C100, TC100, null, null, 7007, null, null, 7010
4, C101, TC102, 7005, null, null, null, null, null

What I intend to accomplish?

Split multiple columns to multiple rows for each record. Example:

id, cliCode, bhCliCode, exchCode
1, C100, TC100, 7005
2, C100, TC100, 7005
3, C100, TC100, 7006
4, C100, TC100, 7007
5, C100, TC100, 7010
6, C101, TC102, 7005
......
......

The other thing that I intended to achieve is to replace values in the columns
to meaningful values before I split multiple columns to multiple rows.

I hope the information that I have given is suffient. Also take note that the query will be written in a stored procedure.

[solomon_13000]

I found the solution:

create procedure usp_tradingprofileexch
as

select bankBrh,
   bhCliCode,
   brhCode,
   email,
   masterAcc,
   phoneNo,
   usFormEffDate,
   usFormExpDate,  
   accSgx = replace(ISNUMERIC(accSgx),1,'SI'),  
   accHk = replace(ISNUMERIC(accHk),1,'HKF'),
   accIdx = replace(ISNUMERIC(accIdx),1,'JK'),
   accUs = replace(ISNUMERIC(accUs),1,'US'),
   accSet = replace(ISNUMERIC(accSet),1,'SET'),
   accPhp = replace(ISNUMERIC(accPhp),1,'PHP')
   INTO #tempTradingProfileExch1
   from ds_lobclient

SELECT * 
INTO #tempTradingProfileExch2
FROM(
SELECT
   bankBrh,
   bhCliCode,
   brhCode,
   email,
   exchCode =
      CASE X.Which
      WHEN 'accSgx' THEN accSgx 
      WHEN 'accHk' THEN accHk
      WHEN 'accIdx' THEN accIdx
      WHEN 'accUs' THEN accUs
      WHEN 'accSet' THEN accSet
      WHEN 'accPhp' THEN accPhp
      END,
   masterAcc,
   phoneNo,
   usFormEffDate,
   usFormExpDate   
FROM
   #tempTradingProfileExch1
   CROSS JOIN (
   SELECT 'accSgx'  
   UNION ALL 
   SELECT 'accHk'
   UNION ALL
   SELECT 'accIdx'
   UNION ALL
   SELECT 'accUs'
   UNION ALL
   SELECT 'accSet'
   UNION ALL
   SELECT 'accPhp'
   ) 
   X (Which) 
   ) t

   insert into tm_tradingprofileexch 
   select * from #tempTradingProfileExch2 t2 where exchCode <> '0' and 
   exchCode not in (select exchCode from tm_tradingprofileexch) and
   bhCliCode not in (select bhCliCode from tm_tradingprofileexch) and
   brhCode not in (select brhCode from tm_tradingprofileexch)