• Status: Solved
• Priority: Medium
• Security: Public
• Views: 310

# Transform columns in lines

I have a table with this fields and i need transform in lines and sum by vat. In some cases the same vat tax could be repeated:

--------------------------------------------------------------------------------------------------
| TX1 | Val1  | Vat1| TX2 | Val2  | Vat2|TX3 | Val3  | Vat3|TX4 | Val4  | Vat4|
--------------------------------------------------------------------------------------------------
| 5% | 10      |0.5   |10%| 15     | 1.5  |16%|10      | 1.6  | 5%| 20     |  1     |

Ex;

%|  val  | vat

5%| 30  | 1,5
10%| 15   |1,5
16%|  20  | 1
0
rflorencio
• 4
• 3
2 Solutions

Commented:
for more on this technique see: Unpivotting by CROSS APPLY and VALUES
``````**[Results]**:

| IDENT |  TX | VAL | VAT |
|-------|-----|-----|-----|
|  rec1 |  5% |  10 | 0.5 |
|  rec1 | 10% |  15 | 1.5 |
|  rec1 | 16% |  10 | 1.6 |
|  rec1 |  5% |  20 |   1 |
``````
That result was produced by the following query (the first column is optional):
``````    CREATE TABLE Table1
([TX1] varchar(3), [Val1] int, [Vat1] money, [TX2] varchar(4), [Val2] int, [Vat2] money, [TX3] varchar(4), [Val3] int, [Vat3] money, [TX4] varchar(3), [Val4] int, [Vat4] int)
;

INSERT INTO Table1
([TX1], [Val1], [Vat1], [TX2], [Val2], [Vat2], [TX3], [Val3], [Vat3], [TX4], [Val4], [Vat4])
VALUES
('5%', 10, 0.5, '10%', 15, 1.5, '16%', 10, 1.6, '5%', 20, 1)
;

**Query 1**:

SELECT
'rec1' AS ident
, ca1.*
FROM table1
CROSS apply (
VALUES
(tx1, val1, vat1),
(tx2, val2, vat2),
(tx3, val3, vat3),
(tx4, val4, vat4)
) ca1 (tx, val, vat)

[1]: http://sqlfiddle.com/#!3/d1863/1
``````
0

Author Commented:
Hi PortletPaul,

For ex. Line 1 and line 4 in your example has vat 5%, it´s possible sum this two lines. Because i need vat tax grouped by tax.
The link you provided not work.
0

Senior .Net ConsultantCommented:
If your data is already in a datatable in your .Net application, I have an article showing how to do the equivalent of a transpose (because it is not natively supported by .net): http://emoreau.com/Entries/Articles/2012/02/ADONet-DataTable-transpose.aspx
0

Commented:
Is this what you mean

``````| IDENT |  TX | VAL | VAT |
|-------|-----|-----|-----|
|  rec1 |  5% |  10 | 0.5 | *
|  rec1 | 16% |  10 | 1.6 | *
21%         2.1  --<< is this what you want as output (not the * rows)

|  rec1 | 10% |  15 | 1.5 |
|  rec1 |  5% |  20 |   1 |
``````
I simply copied the data you displayed in the question. It would be FAR more accurate if you supplied real data - at the moment you will see that the "5%" is a varchar - it appers it should be a calculation. What is actually stored in the table?

& please provide the expected result from the sample data.

& url fixed, it is:
http://www.experts-exchange.com/blogs/PortletPaul/B_7511-Unpivotting-by-CROSS-APPLY-and-VALUES.html
0

Author Commented:
Hi PortletPaul,

For ex. Line 1 and line 4 in your example has vat 5%, i need vat and val grouped by tax.

| IDENT |  TX | VAL | VAT |
|-------  |----- |----- |-----  |
|  rec1 |  5% |  10  | 0.5 |  *
|  rec1 | 10% |  15 | 1.5 |
|  rec1 | 16% |  10 | 1.6 |
|  rec1 |  5% |  20  |   1  | *

* Same vat

What i need:

| IDENT |  TX | VAL | VAT |
|-------  |----- |----- |-----  |
|  rec1 |  5% |  30  | 1.5 |
|  rec1 | 10% |  15 | 1.5 |
|  rec1 | 16% |  10 | 1.6 |
0

Commented:
Thanks, now I know the target :)
This matches the expected results:
`````` **[Results][2]**:

|  TX | VAL | VAT |
|-----|-----|-----|
|  5% |  30 | 1.5 |
| 10% |  15 | 1.5 |
| 16% |  10 | 1.6 |

**Query 1**:

SELECT
tx
, SUM(val) as val
, SUM(vat) as vat
FROM (
SELECT
ca1.tx, ca1.val, ca1.vat
FROM table1
CROSS apply (
VALUES
(tx1, val1, vat1),
(tx2, val2, vat2),
(tx3, val3, vat3),
(tx4, val4, vat4)
) ca1 (tx, val, vat)
) as derived
GROUP BY
tx
ORDER BY
right('00000' + tx,5)
;

------------------------------------------
CREATE TABLE Table1
([TX1] varchar(3), [Val1] int, [Vat1] money, [TX2] varchar(4), [Val2] int, [Vat2] money, [TX3] varchar(4), [Val3] int, [Vat3] money, [TX4] varchar(3), [Val4] int, [Vat4] int)
;

INSERT INTO Table1
([TX1], [Val1], [Vat1], [TX2], [Val2], [Vat2], [TX3], [Val3], [Vat3], [TX4], [Val4], [Vat4])
VALUES
('5%', 10, 0.5, '10%', 15, 1.5, '16%', 10, 1.6, '5%', 20, 1)
;

[1]: http://sqlfiddle.com/#!3/d1863/5
``````
0

Author Commented:
Thanks
0

Commented:
Thanks for the grading. Cheers, Paul
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.