I am very new in the sql server.
Can anyone please help me to generate this Result set in Sql server 2008 and
and how i will get the total in both rows (extremely right row and extremely below row)
Table :
CREATE TABLE PUBLISHER_BATCH
(
PUBLISHER_ID INT,
NAME_PUBLISHER VARCHAR(50),
NO_RECORDS_IMPORTED INT,
NO_RECORDS_EXPORTED INT,
NO_RECORDS_DELETED INT,
STATUS INT,
LOCKED INT,
SOURCE INT
)
INSERT INTO PUBLISHER_BATCH VALUES(1,'Publisher1',18,10,3,' 2',1,2)
INSERT INTO PUBLISHER_BATCH VALUES(2,'Publisher2',28,12,5,' 2',1,2)
INSERT INTO PUBLISHER_BATCH VALUES(3,'Publisher3',25,0,0,' 1',0,1)
but my requirment to display result is like below :
| PUBLISHER_NAME | OnStock | Imported | Exported | Deleted | Total |
| Publisher1 | 0 | 18 | 10 | 3 | 31 |
| Publisher2 | 0 | 27 | 11 | 5 | 43 |
| Publisher3 | 25 | 0 | 0 | 0 | 25 |
| Total | 25 | 45 | 21 | 8 | 99 |
OnStock : Count of all the records where LOCKED=0 and Status=1
I am not able to make the "OnStock" value from the sql query. I am very new in the system..
Can anyone please help me to generate this Result set in Sql server 2008 and
and how i will get the total in both rows (extremely right row and extremely below row)
Thanks in Advance.
Vithal WadjePosted Apr 2, 2015, 4:28 PM
elect *
from
(
select store, week, xCount
from yt
) src
pivot
(
sum(xcount)
for week in ([1], [2], [3])
) piv;