How to solve this Query

Posted by Jayakumars under Sql Server on 12/15/2016 | Points: 10 | Views : 343 | 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


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

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

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