CODEWITHSUNDEEP
sqlsql-server-2008keyvaluepair
Sinan AKYAZICI
Sinan AKYAZICI

Reputation: 3952

How to get an item from key/value table in SQL Server?

I am using SQL Server 2008.

I have a table like this:

+-------------+--------+------------+-----------+--------+
| UnitPriceId | ItemId | ProductKey |    Key    | Value  |
+-------------+--------+------------+-----------+--------+
|           1 |      1 | x          | Quantity  | 50     |
|           2 |      1 | x          | PaperType | 1      |
|           3 |      1 | x          | Price     | 25.00  |
|           4 |      2 | x          | Quantity  | 100    |
|           5 |      2 | x          | PaperType | 1      |
|           6 |      2 | x          | Price     | 40.00  |
|           7 |      3 | x          | Quantity  | 250    |
|           8 |      3 | x          | PaperType | 1      |
|           9 |      3 | x          | Price     | 80.00  |
|          10 |      4 | x          | Quantity  | 500    |
|          11 |      4 | x          | PaperType | 1      |
|          12 |      4 | x          | Price     | 120.00 |
|          13 |      5 | x          | Quantity  | 1000   |
|          14 |      5 | x          | PaperType | 1      |
|          15 |      5 | x          | Price     | 180.00 |
|          16 |      6 | x          | Quantity  | 3000   |
|          17 |      6 | x          | PaperType | 1      |
|          18 |      6 | x          | Price     | 300.00 |
|          19 |      7 | x          | Quantity  | 50     |
|          20 |      7 | x          | PaperType | 2      |
|          21 |      7 | x          | Price     | 30.00  |
|          22 |      8 | x          | Quantity  | 100    |
|          23 |      8 | x          | PaperType | 2      |
|          24 |      8 | x          | Price     | 50.00  |
|          25 |      9 | x          | Quantity  | 250    |
|          26 |      9 | x          | PaperType | 2      |
|          27 |      9 | x          | Price     | 100.00 |
|          28 |     10 | x          | Quantity  | 500    |
|          29 |     10 | x          | PaperType | 2      |
|          30 |     10 | x          | Price     | 150.00 |
|          31 |     11 | x          | Quantity  | 1000   |
|          32 |     11 | x          | PaperType | 2      |
|          33 |     11 | x          | Price     | 220.00 |
|          34 |     12 | x          | Quantity  | 3000   |
|          35 |     12 | x          | PaperType | 2      |
|          36 |     12 | x          | Price     | 350.00 |
|          37 |     13 | x          | Quantity  | 50     |
|          38 |     13 | x          | PaperType | 3      |
|          39 |     13 | x          | Price     | 35.00  |
|          40 |     14 | x          | Quantity  | 100    |
|          41 |     14 | x          | PaperType | 3      |
|          42 |     14 | x          | Price     | 60.00  |
|          43 |     15 | x          | Quantity  | 250    |
|          44 |     15 | x          | PaperType | 3      |
|          45 |     15 | x          | Price     | 120.00 |
|          46 |     16 | x          | Quantity  | 500    |
|          47 |     16 | x          | PaperType | 3      |
|          48 |     16 | x          | Price     | 180.00 |
|          49 |     17 | x          | Quantity  | 1000   |
|          50 |     17 | x          | PaperType | 3      |
|          51 |     17 | x          | Price     | 250.00 |
|          52 |     18 | x          | Quantity  | 3000   |
|          53 |     18 | x          | PaperType | 3      |
|          54 |     18 | x          | Price     | 400.00 |
+-------------+--------+------------+-----------+--------+

I have quantity, papertype and productkey. How can I get the price of that item by query?

The ProductKey should be included to the query.

Because the ProductKey can be different.

Upvotes: 0

Views: 369

Answers (2)

mkb
mkb

Reputation: 1155

I suggest using PIVOT function of T-SQL:

SELECT ItemId, ProductKey, Quantity, PaperType, Price
FROM (
        SELECT ItemId, ProductKey, [Key], Value
        FROM T
      ) AS yadayada
PIVOT (MAX(Value) FOR [Key] IN (Quantity, PaperType, Price)) AS pvt

Result of PIVOT query

(i believe this result is identical with @revoua's query with JOINs):

    | ITEMID | PRODUCTKEY | QUANTITY | PAPERTYPE | PRICE |
    |--------|------------|----------|-----------|-------|
    |      1 |          x |       50 |         1 |    25 |
    |      2 |          x |      100 |         1 |    40 |
    |      3 |          x |      250 |         1 |    80 |
    |      4 |          x |      500 |         1 |   120 |
    |      5 |          x |     1000 |         1 |   180 |
    |      6 |          x |     3000 |         1 |   300 |
    |      7 |          x |       50 |         2 |    30 |
    |      8 |          x |      100 |         2 |    50 |
    |      9 |          x |      250 |         2 |   100 |
    |     10 |          x |      500 |         2 |   150 |
    |     11 |          x |     1000 |         2 |   220 |
    |     12 |          x |     3000 |         2 |   350 |
    |     13 |          x |       50 |         3 |    35 |
    |     14 |          x |      100 |         3 |    60 |
    |     15 |          x |      250 |         3 |   120 |
    |     16 |          x |      500 |         3 |   180 |
    |     17 |          x |     1000 |         3 |   250 |
    |     18 |          x |     3000 |         3 |   400 |

you can query this with a where clause like this:

WHERE PaperType = @PaperType AND Quantity = @Quantity

OR using in a cte

;WITH a1 AS
(
SELECT ItemId, ProductKey, Quantity, PaperType, Price
FROM (
        SELECT ItemId, ProductKey, [Key], Value
        FROM T
      ) AS yadayada
PIVOT (MAX(Value) FOR [Key] IN (Quantity, PaperType, Price)) AS pvt

)
SELECT ItemId, Price, ProductKey 
FROM a1
WHERE PaperType = @PaperType AND Quantity = @Quantity

SQLFIDDLE

Upvotes: 1

revoua
revoua

Reputation: 2059

DECLARE @Quantity int
DECLARE @PaperType int
DECLARE @ProductKey varchar(1)

SET @Quantity = 100
SET @PaperType = 1
SET @ProductKey = 'x'

SELECT T.[Value] Price, T.[ProductKey] FROM T
  JOIN T Q ON (Q.[Key]='Quantity' and T.[ItemId]=Q.[ItemId])
  JOIN T PT ON (PT.[Key]='PaperType' and T.[ItemId]=PT.[ItemId])
WHERE T.[Key]='Price'
  AND Q.[Value]=@Quantity
  AND PT.[Value]=@PaperType
  AND T.[ProductKey]=@ProductKey

Output: 

PRICE   PRODUCTKEY
40  x

SQLFIDDLE

Upvotes: 1

Related Questions