How can I PIVOT table name also as column name?

1

Hi team ,
I have a problem to display table name as a column in select statement.

I am attaching my problem image on which I describe my problem.

Please tell me how can i get this o/p in the select statement?

problempivot.png1534×1205 20 KB

thanks.

2

Here are some temp tables that look like they should dump out what you're looking for. It's using a UNION ALL that includes the name of the table in your join that is manually entered. If it's just a few tables that won't change, then this will work. You might need to do a bunch of stuff if you have a dynamic table name or large set of tables you're including.

Declare @TBL1 TABLE (ID int, Status char(2))
DECLARE @TBL2 TABLE (ID int, Status char(2))

INSERT INTO @TBL1 VALUES(1,'AM'),(2,'AM'),(3,'AU'),(4,'AU')
INSERT INTO @TBL2 VALUES(1,'AM'),(2,'AM'),(3,'AU'),(4,'AU')

select * from (select 'Tbl1' 'TblName', status from @TBL1 UNION ALL select 'Tbl2' 'TblName', status from @TBL2) datatable pivot (count(status) for status in ([AU],[AM])) Pivottable