June 3, 2012 at 10:39 am
i hsvd salary tbl fr employees which consists of empid salary components & amt as rows
i need to create a pivot table so that i have output as
empid basic da ta hra
1 8888
2
June 3, 2012 at 1:28 pm
I would be happy to help if you provide DDL in the form of a CREATE TABLE statement, sample data in the form of INSERT statements and your expected results.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 3, 2012 at 7:02 pm
create table sal(empId nvarchar(10), sal_comp nvarchar(10), IncomeAmount int)
--drop table sal
insert into sal values ('SPIKE', 'basic', 100)
insert into sal values ('SPIKE', 'da', 300)
insert into sal values ('SPIKE', 'hra', 400)
insert into sal values ('SPIKE', 'ta', 500)
insert into sal values ('SPIKE', 'medical', 200)
insert into sal values ('JOHNS', 'basic', 100)
insert into sal values ('JOHNS', 'da', 300)
insert into sal values ('JOHNS', 'hra', 400)
insert into sal values ('JOHNS', 'ta', 500)
insert into sal values ('JOHNS', 'medical', 200)
insert into sal values ('FREDS', 'basic', 100)
insert into sal values ('FREDS', 'da', 300)
insert into sal values ('FREDS', 'hra', 400)
insert into sal values ('FREDS', 'ta', 500)
insert into sal values ('FREDS', 'medical', 200)
here sal_comp is dynamic
need sample qry for both dynamic & also if it was static to have a better understanding
i have
DECLARE @columns VARCHAR(8000)
SELECT @columns = COALESCE(@columns + ',[' + sal_comp + ']', '[' + sal_comp+ ']')
FROM sal
GROUP BY sal_comp
DECLARE @query VARCHAR(8000)
SET @query = '
SELECT *
FROM sal
PIVOT
(
MAX(IncomeAmount)
FOR [sal_comp]
IN (' + @columns + ')
)
AS p'
EXECUTE(@query)
but the output is not proper
June 3, 2012 at 7:08 pm
You can go away from using PIVOT and use a traditional crosstab. This should help clear things up. There is a link to part 1 for static data as well:
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs By Jeff Moden[/url]
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 4, 2012 at 12:50 am
Like This?
--Creating Table
create table sal
(empId nvarchar(10),
sal_comp nvarchar(10),
IncomeAmount int)
--Inserting Sample Data
insert into sal values ('SPIKE', 'basic', 100)
insert into sal values ('SPIKE', 'da', 300)
insert into sal values ('SPIKE', 'hra', 400)
insert into sal values ('SPIKE', 'ta', 500)
insert into sal values ('SPIKE', 'medical', 200)
insert into sal values ('JOHNS', 'basic', 100)
insert into sal values ('JOHNS', 'da', 300)
insert into sal values ('JOHNS', 'hra', 400)
insert into sal values ('JOHNS', 'ta', 500)
insert into sal values ('JOHNS', 'medical', 200)
insert into sal values ('FREDS', 'basic', 100)
insert into sal values ('FREDS', 'da', 300)
insert into sal values ('FREDS', 'hra', 400)
insert into sal values ('FREDS', 'ta', 500)
insert into sal values ('FREDS', 'medical', 200)
--Static Pivot
Select empId, [basic], [da], [hra], [ta], [medical] From sal
Pivot
(max(IncomeAmount) For sal_comp IN ([basic], [da], [hra], [ta], [medical]) ) As pvt
--Dynamic Pivot
Declare @col varchar(max), @sql varchar(max)
Declare @temp table(Cols varchar(50) )
Insert into @temp
Select Distinct sal_comp From sal
Select @col = Coalesce(@col + ', ', '') + Quotename(Cols) From @temp
Set @sql = 'Select empId, '+@col+' From sal
Pivot
(max(IncomeAmount) For sal_comp IN ('+@col+') ) As pvt'
Execute (@sql)
June 4, 2012 at 3:14 am
using the sql that i gve also give s me the same out put a this
but this is not i need
i mean i get the empid repeated
i shld get only one row of each empid
June 4, 2012 at 3:35 am
ssurekha2000 (6/4/2012)
using the sql that i gve also give s me the same out put a thisbut this is not i need
i mean i get the empid repeated
i shld get only one row of each empid
Test your code with this sample Data. I added a duplicate "empid" for "Fred". It works fine.
insert into sal values ('SPIKE', 'basic', 100)
insert into sal values ('SPIKE', 'da', 300)
insert into sal values ('SPIKE', 'hra', 400)
insert into sal values ('SPIKE', 'ta', 500)
insert into sal values ('SPIKE', 'medical', 200)
insert into sal values ('JOHNS', 'basic', 100)
insert into sal values ('JOHNS', 'da', 300)
insert into sal values ('JOHNS', 'hra', 400)
insert into sal values ('JOHNS', 'ta', 500)
insert into sal values ('JOHNS', 'medical', 200)
insert into sal values ('FREDS', 'basic', 100)
insert into sal values ('FREDS', 'da', 300)
insert into sal values ('FREDS', 'hra', 400)
insert into sal values ('FREDS', 'ta', 500)
insert into sal values ('FREDS', 'medical', 200)
insert into sal values ('FREDS', 'basic', 100)
insert into sal values ('FREDS', 'da', 300)
insert into sal values ('FREDS', 'hra', 400)
insert into sal values ('FREDS', 'ta', 500)
insert into sal values ('FREDS', 'medical', 200)
Doesn't fetch duplicates.
Try it.
June 4, 2012 at 4:22 am
no i mean the output needed is
empid 'basic' da ta hra medical
SPIKE' 100 200 100 200 100
JOHNS 100 200 200 200 200
FREDS 100
but instead i am getting empids repeated
June 4, 2012 at 4:30 am
ssurekha2000 (6/4/2012)
no i mean the output needed isempid 'basic' da ta hra medical
SPIKE' 100 200 100 200 100
JOHNS 100 200 200 200 200
FREDS 100
but instead i am getting empids repeated
I still haven't understood what you are trying to say. Can you come up with a better explanation of what you want to do?
June 5, 2012 at 11:00 am
I still haven't understood what you are trying to say. Can you come up with a better explanation of what you want to do?
(S)he probably runs your entire code and gets 2 datasets. Just guessing. 🙂
--Vadim R.
June 5, 2012 at 10:07 pm
rVadim (6/5/2012)
I still haven't understood what you are trying to say. Can you come up with a better explanation of what you want to do?
(S)he probably runs your entire code and gets 2 datasets. Just guessing. 🙂
I have tested the query on the Sample Data and get what I was intending to get. But, maybe we are not understanding what the OP is trying to say. So, that needs a little more explaining from the OP as (S)he clearly thinks that we are missing something.
June 6, 2012 at 9:00 am
I just tested Vinu's Query, and can confirm there are no duplicates returned for the data set provided.
Query :
Select empId, [basic], [da], [hra], [ta], [medical] From sal
Pivot
(max(IncomeAmount) For sal_comp IN ([basic], [da], [hra], [ta], [medical]) ) As pvt
and Output as below:
empId basic da hra ta medical
FREDS 100 300 400 500 200
JOHNS 100 300 400 500 200
SPIKE 100 300 400 500 200
=======================================================================================
Visit my technical reference; you might find some of your issues already documented.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply