CODEWITHSUNDEEP
sqlsql-server
Eisen
Eisen

Reputation: 1897

How can I build a utility matrix table in microsoft SQL Server?

| Store_ID  | item     | 
+ ----------+----------+
|        6  |  Soda    | 
|        8  |  Chips   |
|        9  |  Candy   | 
|        9  |  Soda    |

I basically have the above table. I want to make Store_id the rows and item the columns and have a flag as the values of the table. This is basically a user-interactions matrix/utility matrix.

How can I convert this Table to another Table of the aforementioned form?

Output:

  store_id soda chips candy
  -------------------------
     6      1    0     0
     8      0    1     0
     9      1    0     1

Upvotes: 1

Views: 100

Answers (2)

Evandro de Paula
Evandro de Paula

Reputation: 2642

One approach is to use a dynamic pivot table. Find an example below:

--- QUERY ---

-- Build list of unique item names 
-- CAUTION: Consider using a domain table instead to retrieve the unique item list for performance reasons in case the store table is huge.
DECLARE @Columns AS VARCHAR(MAX)

SELECT
    @Columns = COALESCE(@Columns + ', ','') + QUOTENAME(item)
FROM
    (SELECT DISTINCT item FROM store) AS B
ORDER BY
    B.item


-- Build SQL query
DECLARE @SQL AS VARCHAR(MAX)

SET @SQL = 'SELECT store_id, ' + @Columns + '
FROM
(
 SELECT store_id, item
 FROM   store
) as PivotData
PIVOT
(
   COUNT(item)
   FOR item IN (' + @Columns + ')
) AS PivotResult
ORDER BY store_id';

-- Execute query
EXEC(@SQL)

--- RESULT ---

store_id    Candy       Chips       Soda
----------- ----------- ----------- -----------
6           0           0           1
8           0           1           0
9           1           0           1

(3 rows affected)

Tested on Microsoft SQL Server 2019 (RTM-GDR) (KB4517790) - 15.0.2070.41 (X64)

Upvotes: 1

Joaquín
Joaquín

Reputation: 1136

wit that table design I only come with this solution

 with stores as (
                  select Store_ID = 6, item = 'soda'
                  union all
                  select 8, 'candy'
                  union all
                  select 9, 'candy'
                  union all
                  select 9, 'soda'
                  union all
                  select 9, 'candy'
                  union all
                  select 9, 'soda'
                  union all
                  select 1, 'chips')


                  select    store_id, soda = SUM(CASE WHEN item = 'soda' then 1 else 0 end),
                            candy =  SUM(CASE WHEN item = 'candy' then 1 else 0 end),
                            chips =  SUM(CASE WHEN item = 'chips' then 1 else 0 end)
                  from stores 
                  group by store_id, item

Upvotes: 0

Related Questions