sql query using temp table

Question

I have a query as follows

select VendorNumber,sum(EY_AmountIncl_LC)AmountIncl_LC ,SUm(EY_AmountExcl_LC)AmountExcl_LC,max(EY_datedocumented) Datedocumented
            ,stuff( (select distinct ','+dbo.table2.InvoiceStatus
                           from dbo.table2
                           where dbo.table2.VendorNumber = dbo.table2.VendorNumber 
                           for xml path('')
                          ), 1, 1, ''
                        ) as InvoiceStatus
from dbo.table2
group by VendorNumber

How do i write the same query using temptable in sql server management studio.can anyone help?

Can you show us what you've done so far?

Roman Marusyk
– Roman Marusyk

2018-09-10 10:42:45 +00:00
Commented Sep 10, 2018 at 10:42 — Roman Marusyk
– Roman Marusyk, Commented Sep 10, 2018 at 10:42

Yogesh Sharma · Accepted Answer · 2018-09-10 11:03:07Z

2

First i would correct your subquery condition which should be referenced from outer query :

select VendorNumber, sum(EY_AmountIncl_LC) AmountIncl_LC, 
       max(EY_datedocumented) Datedocumented,
       stuff( (select distinct ','+t22.InvoiceStatus
               from dbo.table2 t22 -- create alias & use them 
               where t22.VendorNumber = t2.VendorNumber 
               for xml path('')
              ), 1, 1, ''
            ) as InvoiceStatus
from dbo.table2 t2 -- create alias & use them
group by VendorNumber;

Now, temp table has same functionality as base table, you just replace your base table name (dbo.table2) with temp table name (#temp whatever name you have).

Short Note about alias :

You can use alias for table name as well as column name.
COLUMN alias are used to make column headings in your result set easier to read.
TABLE alias are used to shorten your SQL to make it easier to read or write, when you are performing a self join or using correlated subquery (ie: listing the same table more than once in the FROM clause).

For more you can visit.

edited Sep 10, 2018 at 11:03

answered Sep 10, 2018 at 10:43

Yogesh Sharma

50.2k5 gold badges31 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

cherrycharitha Over a year ago

I am very new to sql and this concept can you help me in understanding what t22 and t2 are.I know very little things about aliases.can you help me in that.

Collectives™ on Stack Overflow

sql query using temp table

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related