Get the column name based on a record's value

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Get the column name based on a record's value

Author

Topic

inkognito
Starting Member

5 Posts

Posted - 2010-09-02 : 01:08:48

Sample Table
Cust id |SId1|SId2|SId3|SId4|SId5
1| 1| 0| 1| 1| 0
2| 0| 0 |1 | 0 | 0
3| 1| 0 |0 | 0 | 0
4| 0| 0 |0 |0 |0
5 |0 | 0|1|0 |1
6 |0 | 0 |1 |0 |0
7 |0 |1 |0 | 1 |1
8 |0 |0| 0| 0| 1
9 |1 |1 |1 |1 |0
10| 1 |0 |1 |0 |1

Details
Cust id is a primary key
Value of all other fields will be integers between 0 and 5

Sample Query: For cust id 9 and 10 (required cust ids need to be defined in the query), return field name where the sum of the values of the fields is 2
Expected Result: SId1 SId3

Been banging my head against a wall trying to figure this out. Any help on this would be really appreciated
Thanks in advance for the help !!!

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2010-09-02 : 01:14:44

Not sure if I understand your problem fully. What do you mean by this - 'For cust id 9 and 10 (required cust ids need to be defined in the query), '

to retrieve cusitds from your table where sum of values is 2, this should be ok-

select custid from sampletable where (SId1+SId2+SId3+SId4+SId5 )=2

inkognito
Starting Member

5 Posts

Posted - 2010-09-02 : 01:32:54

Hi Saket,
What I meant was that I am using a JDBC connection to run the query. Hence, the Java code would populate which cust ids I need the result for.
Hence, from the perspective of the SQL query, we can consider that the cust ids for which I need the results will be manually added in the query (or more specifically, the required cust ids will be manually written in the query). I dont need the cust ids. I need the field names for which the sum(value of the cell) for the specified cust ids is 2.

Hence, in the example, For cust id 9 and cust id 10, only for fields Sid1 and Sid3 do the value of the cells add up to 2. Hence, the query would return Sid1 and Sid3

quote:
Originally posted by sakets_2000

Not sure if I understand your problem fully. What do you mean by this - 'For cust id 9 and 10 (required cust ids need to be defined in the query), '

to retrieve cusitds from your table where sum of values is 2, this should be ok-

select custid from sampletable where (SId1+SId2+SId3+SId4+SId5 )=2

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2010-09-02 : 01:51:55

So for custid 9, all pair combinations of SId1|SId2|SId3|SId4 sum upto 2. While for custid 10 all pair combinations of SId1|SId3|SId5 sum upto 2. So, You want to display the ones in common between custid 9 and 10?

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2010-09-02 : 01:53:45

Also, You should consider a better design for your table. Things would be a lot easier had you had SId1|SId2|SId3|SId4|SId5 as rows and not columns.

inkognito
Starting Member

5 Posts

Posted - 2010-09-02 : 02:13:25

I thought about that. But the number of cust ids will be huge as compared to the number of SIds. Consider an example of a mobile service customer table with mobile number and the various discount packages he is using. you typically would not use the discount packages as the rows and the mobile numbers as the columns. Like the category says, I am a novice so not too sure how this works :)

Secondly, didnt really get your earlier query. Let me see if I can explain it better

Cust9 Sid1: Value is 1
Cust10 Sid1: Value is 1
Sum of values = 2
Hence, return SId1

Cust9 Sid3: Value is 1
Cust10 Sid3: Value is 1
Sum of values = 2
Hence, return SId3

But yeah, when I think about it, what you say maybe another approach to get the same result

quote:
Originally posted by sakets_2000

So for custid 9, all pair combinations of SId1|SId2|SId3|SId4 sum upto 2. While for custid 10 all pair combinations of SId1|SId3|SId5 sum upto 2. So, You want to display the ones in common between custid 9 and 10?

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2010-09-02 : 02:47:25

ok. Number of SId columns is fixed for now , 5?

inkognito
Starting Member

5 Posts

Posted - 2010-09-02 : 02:55:25

As of now, yes. But there may be a possibility later on to expand it later on. That anyways would not happen periodically

Thanks for your patience and interest on this topic man !!

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2010-09-02 : 03:25:12

If you are on sql 2005 or above, something like this might work

Please test for syntax and output, I don't have sql server installed on my machine.

Select SID
from
    (
    SELECT Cust, SID, Orders
    FROM 
       (
        SELECT Cust, Sid1, Sid2, Sid3, Sid4, Sid5
        FROM sample where cust in (9,10)
        ) p
    UNPIVOT
       (Orders FOR SID IN 
          (Sid1, Sid2, Sid3, Sid4, Sid5)
        )AS unpvt
    )xyz
group by SID
having sum(Orders)=2

inkognito
Starting Member

5 Posts

Posted - 2010-09-02 : 07:00:56

Thanks man ! Will check it and let you know how it goes

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources