按类型选择查询

Question

Table1

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

最后，我们在数据上创建一个数据透视表(以“伪造”该操作符的标准SQL2000方式完成)。然后，我们将“数据透视表”放在一个派生select中，该select只返回我们想要的列，但允许我们对code和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，但会返回重复的值

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