Select other table rows with pivot table

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

I Have Table SalesDetails like

salesid invno amount
1 345 5000
2 678 500
3 111 1000
4 222 2000
3 333 3000

and TaxDetails Table

tdid salesid taxid taxname amount
2 1 1 CST 100
3 1 2 VAT 200
4 1 3 ST 300
5 2 1 VAT 400
6 2 2 ST 500
7 3 1 CST 600
8 3 2 VAT 700
9 3 3 ST 800

by using this query

SQL

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT ',' + QUOTENAME(taxname) 
                    from TaxDetails where salesid=1
                    group by taxname, taxid
                    order by taxid
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = N'SELECT ' + @cols + N' from 
             (
                select amount, taxname
                from TaxDetails where salesid=1
            ) x
            pivot 
            (
                max(amount)
                for taxname in (' + @cols + N')
            ) p '

exec sp_executesql @query;

I got result like this

CST VAT ST
100 200 300

But i want data like this format

salesid invno amount CST VAT ST
1 345 5000 100 200 300
2 678 500 400 500
3 111 1000 600 700 800
4 222 2000 0 0 0
3 333 3000 0 0 0

Please help me

thanks in advance...

What I have tried:

DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)

select @cols = STUFF((SELECT ',' + QUOTENAME(taxname)
from TaxDetails where salesid=1
group by taxname, taxid
order by taxid
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')

set @query = N'SELECT ' + @cols + N' from
(
select amount, taxname
from TaxDetails where salesid=1
) x
pivot
(
max(amount)
for taxname in (' + @cols + N')
) p '

exec sp_executesql @query;

Posted 20-Jun-16 22:21pm

Ravi Sargam

Updated 20-Jun-16 23:29pm

v2

Add a Solution

Comments

CHill60 21-Jun-16 4:31am

It helps if you actually give us the correct table schema - "Invalid column name 'tax'." Use the Improve question link and give us the correct table layout and sample data - specifically table SalesInvoiceTax

Ravi Sargam 21-Jun-16 5:02am

Please check now....... I had given two tables SalesDetails and TaxDetails

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Accepted Answer · 2016-06-20T23:29:00

You need to join the two tables in the source section of your pivot - your sub-query x above.

You also need to remove all of the WHERE clauses where you are limiting the salesid = 1

Because of that you also need to use DISTINCT on the generation of the column list, which means you also need to change the ORDER BY on that section - I've used the shortcut ORDER BY 1 instead of ORDER BY ',' + QUOTENAME(taxname)

This query gets pretty close to your expected results, it's not exact because the sample data given doesn't match your expected results:

SQL

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)
 
select @cols = STUFF((SELECT DISTINCT ',' + QUOTENAME(taxname) 
                    from TaxDetails 
                    group by taxname, taxid
                    order by 1
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')
 
set @query = N'SELECT salesid, invno, ' + @cols + N' from 
             (
                select T.salesid, invno, T.amount, taxname
				from  SalesDetails S
				LEFT OUTER JOIN  TaxDetails T ON T.salesid = S.salesid
            ) x
            pivot 
            (
                max(amount)
                for taxname in (' + @cols + N')
            ) p ORDER BY Salesid'
 
exec sp_executesql @query;