从观察中创建一个稀疏矩阵

I am new to SQL server, I am a R user but R can't be used with my huge dataset (not enough memory).

我是SQL server的新手，我是一个R用户，但是R不能用于我的大型数据集(内存不足)。

What I want to do:

I want to create a sparse matrix from a table with only 2 columns, I dont have any value column, it seems easy but I didn't find the right way to do it.

我想从一个只有2列的表中创建一个稀疏矩阵，我没有任何值列，看起来很简单，但是我没有找到正确的方法。

My data:

ID_patient | ID_product
-----------------------
123            A
123            B
111            C
222            A
333            D
333            E

Ouput wanted:

ID_patient |   A   |   B   |   C   |   D   |   E   |
----------------------------------------------------
123            1       1
111                            1
222            1
333                                    1       1

I have read that I can use the GROUP BY function or Pivot feature but what I have tried so far failed.

我曾经读到过，我可以使用GROUP BY function或Pivot特性，但是到目前为止我尝试过的方法失败了。

Edit

I don't know all the products, so the right way to do that is by using dynamic pivot ?

我不知道所有的乘积，所以正确的方法是使用动态枢轴?

2 个解决方案

#1

You can try something like PIVOT

你可以试试旋转

See demo

看到演示

Select * from 
(select *, copy=Id_product from t)t
pivot
(
    count(copy) for ID_product in ([A],[B],[C],[D],[E]))p

If you don't know A, B, C, D, .. before hand then you should go for dynamic pivot

如果你不知道A B C D在使用之前，你应该使用动态枢轴

update: updated dynamic piv demo

更新:更新动态piv演示

declare @cols nvarchar(max)
declare @query nvarchar(max) 

select @cols=  Stuff((select ','+  quotename( ID_product) from
      (select distinct id_product from t) t  for xml path ('')),1,1,'')

select @query='Select * from 
( select *, copy=Id_product from t ) t
pivot
(count(copy) for ID_product in ( '+@cols+' ))p '
  exec(@query)

#2

Try this

试试这个

IF OBJECT_ID('tempdb..#Temp')IS NOT NULL 
DROP TABLE #Temp

DECLARE @temp AS TABLE (ID_patient INT, ID_product varchar(10))
INSERT INTO @temp
SELECT 123,'A' UNION ALL
SELECT 123,'B' UNION ALL
SELECT 111,'C' UNION ALL
SELECT 222,'A' UNION ALL
SELECT 333,'D' UNION ALL
SELECT 333,'E'  


SELECT * INTO #Temp 
    FROM @temp
DECLARE @Sql nvarchar(max),
        @Col  nvarchar(max),
        @Col2  nvarchar(max)

SELECT @Col=STUFF((SELECT DISTINCT ', '+QUOTENAME(ID_product) FROM #Temp FOR XML PATH ('')),1,1,'')
SELECT @Col2=STUFF((SELECT DISTINCT ', '+'ISNULL('+QUOTENAME(ID_product)+','' '') AS '+QUOTENAME(ID_product) FROM #Temp FOR XML PATH ('')),1,1,'')

SET @Sql='
SELECT ID_patient,'+@Col2+'
FROM
(
SELECT *, DENSE_RANK()OVER( PARTITION BY ID_patient ORDER By ID_patient) AS [Val] FROM #Temp
)AS Src
PIVOT 
(MAX(Val) FOR ID_product IN ('+@Col+')
)AS PVT '
PRINT @Sql
EXEC (@Sql)

Result

结果

ID_patient  A   B   C   D   E
------------------------------
111         0   0   1   0   0
123         1   1   0   0   0
222         1   0   0   0   0
333         0   0   0   1   1

#1