按类型选择查询

Question

表1

Code, desc, type id

01    Rajan    1
01    Sajan    1
01    Vijayan  2
01    Suresh   3
01    Caresh   4
01    Sujesh   4
01    vikran   4
02    desk     1
02    card     2
02    villa    2
02    megash   2
02    supan    3
....

我想按类型id查看表

预期输出

Code type-1 type-2 type-3 type-4

01   Rajan  Vijayan suresh caresh
01   Sajan  null    null   Sujan
01   null   null    null   vikran
02   desk   card    supan  null
02   null   villa   null   null
02   null   megash  null   null

如何针对上述条件进行查询

需要查询帮助

Answer 1

所以首先只是暂存你的数据。请注意，我将添加一个身份行 ID 以供稍后使用。

IF OBJECT_ID('tempdb..#test') IS NOT NULL
   drop table #test
IF OBJECT_ID('tempdb..#Numbered') IS NOT NULL
   drop table #Numbered

 CREATE TABLE #test (Code CHAR(2), [DESC] varchar(10), [type id] INT, RowNumber INT IDENTITY(1,1))


INSERT #test 
    VALUES ('01', 'Rajan', 1),
           ('01' ,'Sajan', 1),
           ('01' ,'Vijayan', 2),
           ('01' ,'Suresh', 3),
           ('01' ,'Caresh', 4),
           ('01' ,'Sujesh', 4),
           ('01' ,'vikran', 4),
           ('02' ,'desk', 1),
           ('02' ,'card' ,2),
           ('02' ,'villa', 2),
           ('02', 'megash', 2),
           ('02', 'supan', 3)

然后我们创建一个保留区域，使用该行 ID 来计算每个名称应该出现在代码的哪一行。

  CREATE TABLE #Numbered
      (
       RowNum int, Code CHAR(2), [type] VARCHAR(10), [DESC] VARCHAR(10)
       )

   INSERT #Numbered
         SELECT (select count(*) from #test where code=t1.Code AND [type id]=t1.[type id] AND  RowNumber<=t1.RowNumber),
                 code, 
                [type id], 
                [DESC]
            FROM #test t1

最后，我们在数据上创建一个 PIVOT 表（以“伪造”该运算符的标准 SQL 2000 方式完成）。然后，我们将该“PIVOT 表”放置在派生选择中，该选择仅返回我们想要的列，但允许我们对代码和 rownum 列进行排序以生成您要求的输出。

     SELECT Code,[type-1],[type-2],[type-3],[type-4]
        FROM (Select P.Code,RowNum
                   , Min( Case When type = '1' Then [DESC] End ) As [type-1]
                   , Min( Case When type = '2' Then [DESC] End ) As [type-2]
                   , Min( Case When type = '3' Then [DESC] End ) As [type-3]
                   , Min( Case When type = '4' Then [DESC] End ) As [type-4]
                      From #Numbered As P
                        Group By P.Code,RowNum) R
        ORDER BY Code,RowNum

如果您需要对此进行进一步解释，请告诉我。

Answer 2

Select t1.code, t1.description, t2.description, t3.description from
(Select code, description from table where type=1) t1, 
(Select code, description from table where type=2) t2, 
(Select code, description from table where type=3) t3,
(Select code, description from table where type=4) t4
Where t1.code=t2.code and t2.code=t3.code and t3.code=t4.code and t4.code=t1.code
and t4.code=t2.code and t1.code=t3.code

尝试这个查询，它不会返回 null，但会返回重复的值，

如果您有任何疑问，请联系