HOW TO CONVERT ROWS TO COLUMN
-
SIR,
I M HAVING TWO TABLES
1) EMPLOYEE
2) SALARY_DETAILS
1) EMPLOYEE HAVING COLUMN
A] EMP_ID INT IDENTITY COL. 1 , 1,
B] EMP_NAME VARCHAR(60),
C] EMP_ADD VARCHAR(150)
2) SALARY_DETAILS HAVING COLUMN
A] EMP_ID INT ,
B] SAL_DATE DATETIME,
C] SAL_AMMOUNT MONEY
I WANT SHOW THE TABLE OUTPUT IN FOLLOWING FORMAT
EMP_NAME JAN FEB MAR APR ...... DEC TOTAL
ABC 100 100 100 100 100 1200
... .. .. .. . .. ... .....
TOTAL TOT_OF TOT_OF .... .... GRANDTOTAL
JAN FRB
HOW DO I WRITE QUERY
I DONT WANT ANY HARDCODE FOR THIS QUERY
-
Have you tried combining the PIVOT function (see basic example on http://msdn.microsoft.com/en-us/library/ms177410.aspx) with SSRS totals for your "footer"? The PIVOT syntax takes some getting used to, but it works well.
-
I M USING SQL SERVER 2000 AND .NET VERSION 1.1
PLZ. SUGGEST FOR THIS VERSION
-
nitinsurve74 (6/16/2008)
SIR,I M HAVING TWO TABLES
1) EMPLOYEE
2) SALARY_DETAILS
1) EMPLOYEE HAVING COLUMN
A] EMP_ID INT IDENTITY COL. 1 , 1,
B] EMP_NAME VARCHAR(60),
C] EMP_ADD VARCHAR(150)
2) SALARY_DETAILS HAVING COLUMN
A] EMP_ID INT ,
B] SAL_DATE DATETIME,
C] SAL_AMMOUNT MONEY
I WANT SHOW THE TABLE OUTPUT IN FOLLOWING FORMAT
EMP_NAME JAN FEB MAR APR ...... DEC TOTAL
ABC 100 100 100 100 100 1200
... .. .. .. . .. ... .....
TOTAL TOT_OF TOT_OF .... .... GRANDTOTAL
JAN FRB
HOW DO I WRITE QUERY
I DONT WANT ANY HARDCODE FOR THIS QUERY
With SQL 2000 you will have to produce a cross tab using a SELECT CASE.
No hard code here. You will end up with something along the lines of
SELECTSRC.SAL_DATE,
MAX(CASE WHEN SRC.SAL_DATE = THEN SRC.Value ELSE NULL END) As [Jan Sales],
MAX(CASE WHEN SRC.SAL_DATE = THEN SRC.Value ELSE NULL END) As [Feb Sales],
MAX(CASE WHEN SRC.SAL_DATE = THEN SRC.Value ELSE NULL END) As [March]
etc
...
FROM
GROUP BY SRC.SAL_DATE
-
You let something do it so you don't have to do it yourself. It's called working smart
Check out Rac, easy and powefull: -
-
I might have answered, but I'm not a "sir".
--
Anye Mercy
"Service Unavailable is not an Error" -- John, ENOM support
"You keep using that word. I do not think it means what you think it means." -- Inigo Montoya in "Princess Bride"
"Civilization exists by geologic consent, subject to change without notice." -- Will Durant
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply