CODEWITHSUNDEEP
sqlsql-server
user9208802
user9208802

Reputation: 41

How do I fetch a value from a table with such a structure in sql?

Table(browser)

+-------+-------------+----------------+--------+
| Users | Application |      Name      | Value  |
+-------+-------------+----------------+--------+
| A1    | AA          | browsername    | IE     |
| A1    | AA          | browserVersion | 11     |
| A1    | AA          | browsername    | chrome |
| A1    | AA          | browserVersion | 60     |
| A1    | AA          | browsername    | safari |
| A1    | AA          | browserVersion | 11     |
| A2    | AA          | browsername    | IE     |
| A2    | AA          | browserVersion | 10     |
| A2    | AA          | browsername    | safari |
| A2    | AA          | browserVersion | 10     |
| A3    | AA          | browsername    | IE     |
| A3    | AA          | browserVersion | 9      |
| A3    | AA          | browsername    | chrome |
| A3    | AA          | browserVersion | 60     |
| A4    | AA          | browsername    | safari |
| A4    | AA          | browserVersion | 11     |
| A4    | AA          | browsername    | IE     |
| A4    | AA          | browserVersion | 10     |
+-------+-------------+----------------+--------+

The user can use the application via any browser. I want to look into the users who used IE browser with 11 version. I'm not able to pick it up as both safari and IE has same versions. Can someone please suggest me how to get the data I require.

Upvotes: 0

Views: 77

Answers (2)

MLeblanc
MLeblanc

Reputation: 1884

First, to make it easier, create a column "position" and make sure they are numbered as they were inserted, like this :

+-------+-------+-------------+----------------+--------+
|  pos  | Users | Application |      Name      | Value  |
+-------+-------+-------------+----------------+--------+
|  1    | A1    | AA          | browsername    | IE     |
|  2    | A1    | AA          | browserVersion | 11     |
|  3    | A1    | AA          | browsername    | chrome |
|  4    | A1    | AA          | browserVersion | 60     |
|  5    | A1    | AA          | browsername    | safari |
|  6    | A1    | AA          | browserVersion | 11     |
|  7    | A2    | AA          | browsername    | IE     |
|  8    | A2    | AA          | browserVersion | 10     |
|  9    | A2    | AA          | browsername    | safari |
|  10   | A2    | AA          | browserVersion | 10     |
|  11   | A3    | AA          | browsername    | IE     |
|  12   | A3    | AA          | browserVersion | 9      |
|  13   | A3    | AA          | browsername    | chrome |
|  14   | A3    | AA          | browserVersion | 60     |
|  15   | A1    | AA          | browsername    | safari |
|  16   | A1    | AA          | browserVersion | 11     |
|  17   | A1    | AA          | browsername    | IE     |
|  18   | A1    | AA          | browserVersion | 10     |
+-------+-------+-------------+----------------+--------+

Now, you can use the CTE and lead function to obtain what you want.

;with X as ( 
   select users, application, name, value as Browser, lead (value) over (order by pos) as Version 
   from table 
   where name = 'browsername'
)
select * 
from X
where browser = 'IE' and version = '11'

PS: always create an identity column, it always pays off in the long run

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270713

You can use conditional aggregation:

select user, application
from t
where name in ('browsername', 'browserVersion')
group by user, application
having sum(case when name = 'browsername' and value = 'IE' then 1 else 0 end) > 0 and
       sum(case when name = 'browserVersion' and value = '11' then 1 else 0 end) > 0 ;

Upvotes: 2

Related Questions