Row to column, column career change, decode,case ... when ... then.

row to column, column change, decode , Case ... when ... then. Case ... when ... then. [when ... then] else ... end



Original:
revisit sql--row and column change
Row to column, column change is the problem we often encounter in the development process. Row-columns are typically implemented by the case-when statement, or by SQL SERVER 2005 's new operator pivot. In the traditional way, better understanding. The level is clear, and more accustomed. But pivot, UNPIVOT provides more syntax than a series of complex select ... The syntax specified in the case statement is simpler and more readable. Here are a few simple examples to introduce the column-change, row-and-column issues.

Let's start with a cliché example, a student's score sheet (simplified below) to figure out the downward row

CREATE  TABLE [Studentscores]

(

   [UserName]         NVARCHAR--Name of        student

    [Subject]          NVARCHAR (a)-        -subjects

    [Score]            FLOAT,               --Score

)

INSERT into [studentscores] SELECT ' Nick ', ' language ', 80

INSERT into [studentscores] SELECT ' Nick ', ' math ', 90

INSERT into [studentscores] SELECT ' Nick ', ' English ', 70

INSERT into [studentscores] SELECT ' Nick ', ' creature ', 85

INSERT into [studentscores] SELECT ' Kent ', ' language ', 80

INSERT into [studentscores] SELECT ' Kent ', ' math ', 90

INSERT into [studentscores] SELECT ' Kent ', ' English ', 70

INSERT into [studentscores] SELECT ' Kent ', ' creature ', 85

If I want to know each student's grade, and each student's score line, so that I see, statistics, export data

      MAX (case Subject when ' language ' THEN Score ELSE 0 end) as ' language ',

      MAX (case Subject when ' math ' THEN Score ELSE 0 end) as ' math ',

      MAX (case Subject when ' English ' THEN Score ELSE 0 end) as ' English ',

      MAX (case Subject when ' creature ' THEN Score ELSE 0 end) as ' creature '

FROM dbo. [Studentscores]

GROUP by UserName

The results of the query are shown in the figure, so that we can get a clear picture of each student's performance.

Next we'll look at the second one. There is a game player recharge list (just to illustrate, a small example),

CREATE TABLE [Inpours]

(

   [ID]                

   [UserName]          NVARCHAR,--Gamers  

    [Createtime]        DATETIME,      --Recharge time    

    [PayType]           NVARCHAR,  --Recharge type    

    [Money]             DECIMAL,       --Recharge amount

    [Issuccess]         BIT,           --whether success 1 indicates success, 0 indicates failure

    CONSTRAINT [pk_inpours_id] PRIMARY KEY (ID)

)

INSERT into Inpours SELECT ' John ', ' 2010-05-01 ', ' Alipay ', 50, 1

INSERT into Inpours SELECT ' John ', ' 2010-06-14 ', ' Alipay ', 50, 1

INSERT into Inpours SELECT ' John ', ' 2010-06-14 ', ' Mobile SMS ', 100, 1

INSERT into inpours SELECT ' dick ', ' 2010-06-14 ', ' Mobile SMS ', 100, 1

INSERT into inpours SELECT ' dick ', ' 2010-07-14 ', ' Alipay ', 100, 1

INSERT into Inpours SELECT ' Harry ', ' 2010-07-14 ', ' ICBC card ', 100, 1

INSERT into Inpours SELECT ' Zhao Liu ', ' 2010-07-14 ', ' Build bank card ', 100, 1

Here is a statistical data requirements, the request by date, payment method to statistical recharge amount information. This is also a typical example of row-forwarding. We can use the following script to achieve the destination code SELECT CONVERT (VARCHARTen), Createtime, the) as Createtime,
Case PayType when ' Alipay ' THEN SUM (Money) ELSE0End as ' Alipay ',
Case PayType when ' mobile SMS ' THEN SUM (Money) ELSE0End as ' Mobile SMS ',
Case PayType when ' ICBC card ' THEN SUM (Money) ELSE0End as ' ICBC card ',
Case PayType when ' build bank card ' THEN SUM (Money) ELSE0End as ' Construction Bank card '
From Inpours
GROUP by Createtime, PayType

As shown in the figure, we just get this output, and we need to deal with it further to get the results we want.

       ISNULL (SUM ([Alipay])    , 0) as  [Alipay]    

       ISNULL ([Mobile Short message])  , 0 as  [mobile SMS]   

       ISNULL (SUM ([ICBC card]), 0 as  [ICBC card],  

       ISNULL ([Construction Bank card]), 0 as  [Construction Bank card]

From

(

    SELECT CONVERT (VARCHAR, Createtime,) as Createtime,

           Case PayType when ' Alipay '     THEN SUM (Money) ELSE 0 end as ' Alipay ',

           Case PayType when ' mobile SMS '   THEN SUM (Money) ELSE 0 end as ' Mobile SMS ',

           Case PayType when ' ICBC card ' THEN SUM (Money) ELSE 0 end as ' ICBC card ',

           Case PayType when ' build bank card ' THEN SUM (Money) ELSE 0 end as ' Build bank card '

    From Inpours

    GROUP by Createtime, PayType

) T

GROUP by Createtime

The key to the implementation of the column is to clarify the logic, and the concept of Grouping (group by) is more clear. The top two columns are basically the type of row-and-column. But there's a problem here, and it's a simple illustration I made to illustrate. In practice, there may be many ways to pay, and the logic is much more complex, it may involve exchange rates, fees and so on (once such a), if the payment method is particularly large, our case will be a lot of, really annoyed, and a new way of payment, We have to modify the script. If you rewrite the script with dynamic SQL, we can easily solve the problem code DECLARE @cmdText VARCHAR (8000);
DECLARE @tmpSql VARCHAR (8000);

SET @cmdText = ' SELECT CONVERT (VARCHAR (a), Createtime,) as Createtime, ' + CHAR (Ten);
SELECT @cmdText = @cmdText + ' case paytype when ' + PayType + ' "' THEN SUM (money) ELSE 0" "+ Payt Ype
+ ', ' + CHAR (Ten) from (SELECT DISTINCT paytype from Inpours) T

SET @cmdText = Left (@cmdText, LEN (@cmdText)-2)--Note that if you do not add char (10) use Left (@cmdText, LEN (@cmdText)-1)

SET @cmdText = @cmdText + ' from Inpours GROUP by Createtime, PayType ';

SET @tmpSql = ' SELECT createtime, ' + CHAR (Ten);
SELECT @tmpSql = @tmpSql + ' ISNULL (SUM (' + PayType + '), 0) as ' + PayType + ', ' + CHAR (Ten)
From (SELECT DISTINCT paytype from Inpours) T

SET @tmpSql = Left (@tmpSql, LEN (@tmpSql)-2) + ' from (' + CHAR ' (Ten);

SET @cmdText = @tmpSql + @cmdText + ') T GROUP by Createtime ';
PRINT @cmdText
EXECUTE (@cmdText);

The following is the use of row-and-column pivot, you can compare, it is really simple, more readable (hehe, custom premise) code select  
         createtime,  [Alipay] ,  [Mobile SMS], 
          [ICBC Card] ,  [Construction Bank card] 
from 
( 
     select  CONV ERT ( VARCHAR ), createtime,  )   as createtime,paytype,  money 
     from inpours 
) p 
PIVOT ( 
  &nbs p;          SUM (Money)  
              for paytype  in 
             ([Alipay],  [Mobile SMS], ] [