Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2010
    Posts
    3

    Unanswered: SQL Dynamic Columns

    I have a .NET page which allows for a user to upload an excel file, this excel file contains client data, and that data is imported into SQL, and then the excel file is deleted. So, I am left with a ClientBatch table, which stores information about each data batch (batch is each time an excel file is uploaded), and a ClientData table, which holds all the client data. The ClientData table has various fields which need to be compared, and then reported on. This is where I'm having trouble... say I want to look into a field called ClientType, I want to use this SQL command ( SELECT DISTINCT [ClientType] FROM [dbo].[CustomerData] ) to populate the column names, then count how many instances of each type there are. For example, if there are 9 records, and ClientType values are as follows; DD DD DV DV DD DL DL DV DD, I want the table to look like this....

    Code:
    Batch   |   DD   |   DV   |   DL   |
    ====================================
    1           3         2       2 
    2 .................
    Hopefully that gives you a good idea of what I'm looking for... any ideas?

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Code:
    select
    BatchID
    ,[DD] =sum(case ClientType when 'DD' then 1 else 0 end)
    ,[DL] =sum(case ClientType when 'DL' then 1 else 0 end)
    ,[DV] =sum(case ClientType when 'DV' then 1 else 0 end)
    from (
    select BatchID = 4, ClientType ='DD', ClientID = 1 union all
    select 3,'DD', 12 union all
    select 3,'DV', 55 union all
    select 2,'DD', 1 union all
    select 3,'DD', 12 union all
    select 2,'DV', 15 union all
    select 2,'DV', 17 union all
    select 4,'DV', 18 union all
    select 3,'DV', 21 union all
    select 3,'DL', 51 union all
    select 2,'DL', 41
    ) x
    group by BatchID
    
    Last edited by rdjabarov; 07-19-10 at 16:12. Reason: formatting
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Jul 2010
    Posts
    3
    Thank you, but that doesn't populate the column names dynamically ... see, there could be DX, DP and DQ as well, which is why I want the columns dynamically populated, and the data to be a count of that column.

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    I gave you a starting point and a concept. Follow the example to add other ClientTypes.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •