按类型选择查询

问题描述

表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

如何查询上述条件

需要查询帮助

推荐答案

因此，首先要暂存数据.请注意，我将添加一个标识行ID，以供以后使用.

So first off just staging your data up. Note that I'm adding an identity row id for later.

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来计算每个名称应继续在代码的哪一行.

Then we create a holding area that uses that row id to calculate which row of the code each of the names should go on.

  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列进行排序以生成所需的输出.

Lastly, we create a PIVOT table on the data, (done in a standard SQL 2000 way of "faking" that operator). We then place that "PIVOT table" in a derived select that returns only the columns we want but allows us to sort on the code and rownum columns to generate the output you asked for.

     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

如果您需要进一步解释，请告诉我.

Please let me know if you want further explanation on any of this.

这篇关于按类型选择查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！