How to solve this Query

Posted by Jayakumars under Sql Server on 12/15/2016 | Points: 10 | Views : 468 | Status : [Member] [MVP] | Replies : 5
Hi

How to solve this Query using Sql Server only

CREATE TABLE Titlepay
(
ID INT PRIMARY KEY IDENTITY(1,1),
TITLENAME VARCHAR(50)
)

CREATE TABLE Detailpay
(
ID INT PRIMARY KEY IDENTITY(1,1),
TITLEID VARCHAR(50),
PAYAMOUNT DECIMAL(18,2),
EMPID INT
)

--iNSERT INTO Titlepay VALUES('Hra'),('Da'),('Ca'),('Pf'),('Lic')
--Insert into Detailpay values(1,100,1001),(2,250,1001),(3,350,1001),(4,450,1001),(5,600,1001)
--Insert into Detailpay values(1,101,1002),(2,251,1002),(3,351,1002),(4,451,1002),(5,601,1002)


-- I need output like this

-- EmpID Hra Da Ca Pf Lic
-- 1001 100 250 350 450 600
-- 1002 101 251 351 451 601

Mark as Answer if its helpful to you

Kumaraspcode2009@gmail.com



Responses

Posted by: A2H on: 12/15/2016 [Member] [MVP] Silver | Points: 25

Up
0
Down
You can use PIVOT Function in SQL Server.

Sample Query
SELECT PivotTable.EMPID AS EmpID, 
[1] AS 'Hra', [2] AS 'Da',[3] AS 'Ca',[4] AS 'Pf',[5] AS 'Lic'
FROM
(SELECT EMPID, CONVERT(INT,PAYAMOUNT) AS PayAmount,TITLEID
FROM Detailpay) AS SourceTable
PIVOT
(
AVG(PAYAMOUNT)
FOR TITLEID IN ([1], [2],[3],[4],[5])
) AS PivotTable;


Thanks,
A2H
My Blog

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: A2H on: 12/15/2016 [Member] [MVP] Silver | Points: 25

Up
0
Down
Reference URL : https://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx

Thanks,
A2H
My Blog

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Jayakumars on: 12/17/2016 [Member] [MVP] Bronze | Points: 25

Up
0
Down
hi

how to set
 isnull(PAYAMOUNT,0) ?


can you altered and updated

I canot set isnull for amount field in pivot table can you guide me.

Mark as Answer if its helpful to you

Kumaraspcode2009@gmail.com

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Jayakumars on: 12/17/2016 [Member] [MVP] Bronze | Points: 25

Up
0
Down
Hi

I have create one function that function some value retured so

how to include that function value in this Query

this is function calling

select dbo.fnNumber(100)


SELECT PivotTable.EMPID AS EmpID, dbo.fnNumber(100),

[1] AS 'Hra', [2] AS 'Da',[3] AS 'Ca',[4] AS 'Pf',[5] AS 'Lic'
FROM
(SELECT EMPID, CONVERT(INT,PAYAMOUNT) AS PayAmount,TITLEID
FROM Detailpay) AS SourceTable
PIVOT
(
AVG(PAYAMOUNT)
FOR TITLEID IN ([1], [2],[3],[4],[5])
) AS PivotTable;


i tried and included but not working

can you altered this one.

Mark as Answer if its helpful to you

Kumaraspcode2009@gmail.com

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: A2H on: 12/17/2016 [Member] [MVP] Silver | Points: 25

Up
0
Down
You cannot place the IsNull() until after the data is selected so you will place the IsNull() around the final value in the SELECT:


SELECT ISNULL(PivotTable.EMPID,'') AS EmpID,
ISNULL([1],0) AS 'Hra', ISNULL([2],0) AS 'Da',ISNULL([3],0) AS 'Ca',ISNULL([4],0) AS 'Pf',ISNULL([5],0) AS 'Lic'
FROM
(SELECT Distinct EMPID,CONVERT(INT,PAYAMOUNT) AS PayAmount,TITLEID
FROM Detailpay) AS SourceTable
PIVOT
(
AVG(PAYAMOUNT)
FOR TITLEID IN ([1], [2],[3],[4],[5])
) AS PivotTable;


Thanks,
A2H
My Blog

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response