TSQL query based on IF?

Question

This the sample of my data:

+----+---+----+----+----+----+----+-------------+------------+-------------+
| ID | C | C1 | C2 | C3 | C4 | C5 | EndingPoint | TransferID | Transferred |
+----+---+----+----+----+----+----+-------------+------------+-------------+
| 1  | A | A  |    |    |    |    | B           | 1          | 80          |
+----+---+----+----+----+----+----+-------------+------------+-------------+
| 2  | A | A  | B  |    |    |    | C           | 2          | 40          |
+----+---+----+----+----+----+----+-------------+------------+-------------+
| 3  | A | A  | B  | C  |    |    | A           | 3          | 10          |
+----+---+----+----+----+----+----+-------------+------------+-------------+
| 4  | B | B  |    |    |    |    | C           | 1          | 25          |
+----+---+----+----+----+----+----+-------------+------------+-------------+
| 5  | B | B  | C  |    |    |    | A           | 2          | 30          |
+----+---+----+----+----+----+----+-------------+------------+-------------+
| 6  | C | C  |    |    |    |    | A           | 1          | 70          |
+----+---+----+----+----+----+----+-------------+------------+-------------+

I need to generate Temporary Table? or WITH clause, that will look like:

In case that TransferID = 1 THEN it will take the string from C1 and EndPoint and value from Transferred:

+------+----+------------+
| From | To |Transferred |
+------+----+------------+
|   A  |  B |     80     |
+------+----+------------+

+------+----+------------+
| From | To |Transferred |
+------+----+------------+
|   B  |  C |     25     |
+------+----+------------+

+------+----+------------+
| From | To |Transferred |
+------+----+------------+
|   C  |  A |     70     |
+------+----+------------+

In case than TransferID=2 THEN: It will take the value from C1 and C2 and value from Transferred. The next row would be then the value from C2 and EndPoint and value from Transferred:

+------+----+------------+
| From | To |Transferred |
+------+----+------------+
|   A  |  B |     40     |
+------+----+------------+
|   B  |  C |     40     |
+------+----+------------+

+------+----+------------+
| From | To |Transferred |
+------+----+------------+
|   B  |  C |     30     |
+------+----+------------+
|   C  |  A |     30     |
+------+----+------------+

In Case that TransferID=3 THEN: It will take the value from C1 and C2 and value from Transferred. The next row would be then the value from C2 and C3 and value from Transferred.

The next row would be then the value from C3 and EndPoint and value from Transferred

+------+----+------------+
| From | To |Transferred |
+------+----+------------+
|   A  |  B |     10     |
+------+----+------------+
|   B  |  C |     10     |
+------+----+------------+
|   C  |  A |     10     |
+------+----+------------+

And so on up to TransferID=5

And then from Temporary Table or With Selection (not sure what will work better), I will select the SUM of Transferred, GROUP BY From, To.

I am using MS SQL 2008 and SQL Fiddle is here

Fiddle Code :

Create TABLE T (
  ID int NOT NULL,
   C varchar(5) NOT NULL,
  C1 varchar(5),
  C2 varchar(5),
  C3 varchar(5),
  C4 varchar(5),
  C5 varchar(5),
  EndingPoint varchar(5) NOT NULL,
  TransferID int NOT NULL,
  Transferred int);

INSERT INTO T VALUES (1,'A','A','','','','','B',1,80);
INSERT INTO T VALUES (2,'A','A','B','','','','C',2,40);
INSERT INTO T VALUES (3,'A','A','B','C','','','A',3,10);
INSERT INTO T VALUES (4,'B','B','','','','','C',1,25);
INSERT INTO T VALUES (5,'B','B','C','','','','A',2,30);
INSERT INTO T VALUES (6,'C','C','','','','','A',1,70);

Many thanks in advance!

Answer 1

When I change my @TransferID to each of your specified numbers(1 through 3), I get the same results you do. Hope this helps!

SQL Server 2005 and Above Solution

DECLARE @TransferID INT = 1;

WITH CTE_Unpivot
AS
(
    SELECT EndingPoint,TransferID,val,col,ID,Transferred
    FROM T
    UNPIVOT
    (
        val FOR col IN (C1,C2,C3,C4,C5)
    ) unpvt
    WHERE       col <= 'C' + CAST(@TransferID AS VARCHAR(5))
            AND TransferID <= @TransferID
)

SELECT DISTINCT VAL,CA2.EndingPoint,CA.Transferred
FROM CTE_Unpivot A
CROSS APPLY
(
    SELECT TOP 1 Transferred
    FROM CTE_Unpivot
    WHERE       val = A.val
            AND col = A.col
            AND TransferID = @TransferID
    ORDER BY ID
) CA
CROSS APPLY
(
    SELECT TOP 1 EndingPoint
    FROM CTE_Unpivot
    WHERE       val = A.val
            AND col = A.col
    ORDER BY ID
) CA2
WHERE val != ''

Answer 2

You can unpivot those C1-EndingPoint columns and then use the Lead function and order by the C1 to EndingPoint column names (which happen to be in correct order)...

http://sqlfiddle.com/#!3/61e519/2

select * from (
    select
        ID,
        [From] = pt,
        [To] = Lead(pt, 1) over(partition by Id order by col),
        [Transferred]
    from
        T
        unpivot(
            pt for col in (C1, C2, C3, C4, C5, EndingPoint)
        ) unp
    where
        pt <> ''
) t
where 
    [To] is not null

SQL 2008 version without using Lead function...

http://sqlfiddle.com/#!3/61e519/5

with cte as (
    select
        ID,
        [From] = pt,
        rn = row_number() over(partition by Id order by col),
        [Transferred]
    from
        T
        unpivot(
            pt for col in (C1, C2, C3, C4, C5, EndingPoint)
        ) unp
    where
        pt <> ''
)

select
  c.[ID],
  c.[From],
  [To] = n.[From],
  c.[Transferred]
from
  cte c
  inner join cte n on n.ID = c.ID and n.rn = c.rn + 1

Answer 3

Here is the SQL2008 version of the @dotjo's solution:

;with r as (
 select *
 from T
  unpivot(pt for col in (C1, C2, C3, C4, C5, EndingPoint)) unp
 where pt!='')

  select r.pt FromPt, r2.pt ToPt, sum(r.Transferred) Transferred
  from r
    cross apply (select top 1 *
                from r r2
                where r.ID=r2.ID
                  and r.col<r2.col
                order by r2.col) r2
  group by r.pt, r2.pt

Answer 4

You can implement the logic using a bunch of union all statements:

select c1 as frompt, endingpoint as topt, transferred
from t
where transferred = 1
union all
select c1 as frompt, c2 as topt, transferred
from t
where transferred = 2
union all
select c2 as frompt, endingpoint as topt, transferred
from t
where transferred = 2
union all
select c1 as frompt, c2 as topt, transferred
from t
where transferred = 3
union all
select c2 as frompt, c3 as topt, transferred
from t
where transferred = 3
union all
select c3 as frompt, endingpoint as topt, transferred
from t
where transferred = 3;

This can actually be simplified to:

select (case when transferred = 1 then c1
             when transferred = 2 then c2
             when transferred = 3 then c3
        end) as frompt, endingpoint as topt, transferred
from t
union all
select c1, c2, transferred
from t
where transferred >= 2
union all
select c2, c3, transferred
from t
where transferred >= 3;

Note: the resulting format is more normalized and it is a better structure for the data and the version you are storing.