BorlandTalk.com

Pawe³ Zalewski · Guest

Guys help needed,

I have a table X

DocNo | InvNo | VAT | Anount
----------------------------------------
1 | a1 | 7 | 100
1 | a1 | 22 | 200
2 | a2 | 0 | 50
3 | a3 | 22 | 300
3 | a3 | 7 | 10
3 | a3 | 0 | 30

I need to create a view which will look like this

DocNo | InvNo | VAT7 | Am7 | VAT22 | Am22 | VAT0 | Am0
-------------------------------------------------------------------------
1 | a1 | 7 | 100 | 22 | 200 | 0
| 0
2 | a2 | 0 | 0 | 0 | 0 |
0 | 50
3 | a3 | 7 | 10 | 22 | 300 | 0
| 30

Thanks
Paul

Simon Owen · Guest

Yu can do this using sub-queries, but they will be slow:

SELECT A.DocNo,
A.InvNo,
(SELECT B.VAT FROM TableX B Where B.DocNo = A.DocNo AND B.VAT
= 7) As VAT7,
(SELECT B.Amount FROM TableX B Where B.DocNo = A.DocNo AND
B.VAT = 7) As Am7,
(SELECT B.VAT FROM TableX B Where B.DocNo = A.DocNo AND B.VAT
= 22) As VAT22,
(SELECT B.Amount FROM TableX B Where B.DocNo = A.DocNo AND
B.VAT = 22) As Am22,
(SELECT B.VAT FROM TableX B Where B.DocNo = A.DocNo AND B.VAT
= 0) As VAT0,
(SELECT B.Amount FROM TableX B Where B.DocNo = A.DocNo AND
B.VAT = 0) As Am0
FROM
TableX A
GROUP BY
A.DocNo

The above is an example using Ansi-SQL query as you failed to specify which
SQL server you are using,, it assumes that the records are grouped by DocNo.
There are ways to optimize the above but it is dependent on the system.