SQL Server 2008 R2 using PIVOT with varchar columns not working
When you are using the PIVOT function the values inside the IN clause need to match a value that you are selecting. Your current data does not include 1, 2, or 3. You can use row_number() to assign a value for each x:
select x, [1], [2], [3]
from
(
select x, value,
row_number() over(partition by x order by y) rn
from test
) d
pivot
(
max(value)
for rn in ([1], [2], [3])
) piv;
See SQL Fiddle with Demo. If you then have a unknown number of values for each x, then you will want to use dynamic SQL:
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT distinct ',' + QUOTENAME(row_number() over(partition by x order by y))
from test
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT x,' + @cols + '
from
(
select x, value,
row_number() over(partition by x order by y) rn
from test
) x
pivot
(
max(value)
for rn in (' + @cols + ')
) p '
execute(@query);
See SQL Fiddle with Demo
SQL Server 2008 R2: Pivot table on VARCHAR column
You can GROUP BY employee and use conditional counting in the HAVING clause, to get the employee that satisfies the criteria:
SELECT ID, EmpName
FROM EMPDetails
GROUP BY ID, EmpName
HAVING COUNT(CASE
WHEN ColumnName = 'Company' AND
ColumnValue = 'Microsoft' THEN 1
END) > 0
AND
COUNT(CASE
WHEN ColumnName = 'Profession' AND
ColumnValue = 'Database' THEN 1
END) > 0
The above query picks employees having Microsoft as a Company and Database as a Profession.
Output:
ID EmpName
------------
1 S
You can now use conditional aggregation to pivot the employee rows:
SELECT ID, EmpName,
'Microsoft' AS Company,
'Database' AS Profession,
MAX(CASE WHEN ColumnName = 'Location' THEN ColumnValue END) AS Location
FROM EMPDetails
GROUP BY ID, EmpName
HAVING COUNT(CASE
WHEN ColumnName = 'Company' AND
ColumnValue = 'Microsoft' THEN 1
END) > 0
AND
COUNT(CASE
WHEN ColumnName = 'Profession' AND
ColumnValue = 'Database' THEN 1
END) > 0
There is no need to perform aggregation for the Company or the Profession value, as the desired value is guaranteed to belong to the employee group.
Output:
ID EmpName Company Profession Location
-----------------------------------------
1 S Microsoft Database USA
To get the second result set, you can simply replace Database by Software:
SELECT ID, EmpName,
'Microsoft' AS Company,
'Software' AS Profession,
MAX(CASE WHEN ColumnName = 'Location' THEN ColumnValue END) AS Location
FROM EMPDetails
GROUP BY ID, EmpName
HAVING COUNT(CASE
WHEN ColumnName = 'Company' AND
ColumnValue = 'Microsoft' THEN 1
END) > 0
AND
COUNT(CASE
WHEN ColumnName = 'Profession' AND
ColumnValue = 'Software' THEN 1
END) > 0
Output:
ID EmpName Company Profession Location
---------------------------------------------
3 R Microsoft Software UK
2 U Microsoft Software UK
PIVOT operator not working in SQL Server 2008 R2
In the SELECT statement add escape [ and ] for the column names 2005 and 2006, like [2005], [2006] will solve the issue.
Sample execution with the given data:
DECLARE @GenderYearWise TABLE ([Year] INT, Gender VARCHAR (100), Total INT);
INSERT INTO @GenderYearWise ([Year], Gender, Total) VALUES
(2005, 'Female' , 374),
(2005, 'Male' ,1579),
(2006, 'Female' , 853),
(2006, 'Male' ,4769);
Select
Gender, [2005], [2006]
From
@GenderYearWise
PIVOT
(SUM(total)
FOR [Year] IN ([2005], [2006])
) AS PitvotTable
Result:
Gender 2005 2006
Female 374 853
Male 1579 4769
Also escape column name for the reserved keyword Year.
SQL Server 2008 R2: Dynamic pivot query
using PIVOT we can achieve this
DECLARE @sales table
(
SalesCountry varchar(20),
SalesState varchar(20),
SalesMan varchar(20)
);
insert into @sales values('USA','TEXAS','Mak');
insert into @sales values('USA','California','Sam');
insert into @sales values('Cannada','Alberta','John');
insert into @sales values('Cannada','Manitoba','John');
Select SalesCountry,MAX([1]) State1,MAX([2]) State2,MAX([Sales_1])[Sales_1],MAX([Sales_2])[Sales_2] FROM (
select
SalesCountry,
SalesState,
SalesMan,
ROW_NUMBER()OVER(PARTITION BY SalesCountry ORDER BY SalesCountry)RN ,
'Sales'+'_'+CAST(ROW_NUMBER()OVER(PARTITION BY SalesCountry ORDER BY SalesCountry) AS VARCHAR)RNN
FROM @sales
)T
PIVOT (MAX(SalesState) for RN IN ([1],[2]))P
PIVOT (MAX(SalesMan) for RNN IN ([Sales_1],[Sales_2]))PP
GROUP BY PP.SalesCountry
SQL Server 2008 R2 - Pivot Usage?
To achieve this you first need to UNPIVOT your table and then PIVOT again. One of the complications when you unpivot columns with different data types is that you have to cast them all to the same datatype and most likely to transform some of them (e.g. dates, percentages etc) . In the example below VARCHAR(32) is used as a common data type. You might want to adjust it to your needs.
Here is a working query with only some of your columns:
WITH unpivot_phase AS
(
SELECT property, pinterval, value
FROM
(
SELECT pinterval,
CAST(phasetype AS VARCHAR(32)) phasetype,
CAST(abegdate AS VARCHAR(32)) abegdate,
CAST(abegdatehr AS VARCHAR(32)) abegdatehr,
CAST(penddate AS VARCHAR(32)) penddate,
CAST(pendhour AS VARCHAR(32)) pendhour,
CAST(alength AS VARCHAR(32)) alength,
CAST(avglength AS VARCHAR(32)) avglength,
CAST(achgprevhilo AS VARCHAR(32)) achgprevhilo,
CAST(avgchgprev AS VARCHAR(32)) avgchgprev,
CAST(achgprevhilopct AS VARCHAR(32)) achgprevhilopct,
CAST(avgchgprevpct AS VARCHAR(32)) avgchgprevpct,
CAST(adatehilo AS VARCHAR(32)) adatehilo
-- add rest of the columns here
FROM phasepivot
) s
UNPIVOT
(
value FOR property IN (phasetype, abegdate, abegdatehr,
penddate, pendhour, alength, avglength,
achgprevhilo, avgchgprev, achgprevhilopct,
avgchgprevpct, adatehilo) -- add rest of the columns here
) u
)
SELECT property, H, D, W, M
FROM
(
SELECT property, pinterval, value
FROM unpivot_phase
) s
PIVOT
(
MAX(value) FOR pinterval IN (H, D, W, M)
) p
ORDER BY
CASE property
WHEN 'phasetype' THEN 1
WHEN 'abegdate' THEN 2
WHEN 'abegdatehr' THEN 3
WHEN 'penddate' THEN 4
WHEN 'pendhour' THEN 5
WHEN 'alength' THEN 6
WHEN 'avglength' THEN 7
WHEN 'achgprevhilo' THEN 8
WHEN 'avgchgprev' THEN 9
WHEN 'achgprevhilopct' THEN 10
WHEN 'avgchgprevpct' THEN 11
WHEN 'adatehilo' THEN 12
END
Output:
| PROPERTY | H | D | W | M |
|-----------------|------------|------------|------------|------------|
| phasetype | 1 | 1 | 2 | 2 |
| abegdate | 2013-09-26 | 2013-09-25 | 2013-09-09 | 2012-01-03 |
| abegdatehr | 3 | 0 | 0 | 0 |
| penddate | 2013-09-27 | 2013-10-01 | 2013-11-18 | 2013-02-04 |
| pendhour | 1 | 0 | 0 | 0 |
| alength | 12 | 3 | 3 | 21 |
| avglength | 6.00 | 5.00 | 11.00 | 14.00 |
| achgprevhilo | -16.74 | -37.98 | 97.18 | 501.71 |
| avgchgprev | -20.98 | -55.49 | 129.06 | 330.90 |
| achgprevhilopct | -0.98 | -2.19 | 5.96 | 41.92 |
| avgchgprevpct | -1.53 | -4.42 | 11.60 | 34.09 |
| adatehilo | 2013-09-27 | 2013-09-25 | 2013-09-16 | 2013-07-01 |
Here is SQLFiddle demo
Getting Null data while using PIVOT in sql server 2008 R2
I think this will help you.we should use QuoteName function for space column.
CREATE Table #MEMBER_ATTRIBUTES (
MA_ID int,
MEMBER_ID int,
MEMBER_PROPERTY varchar(100),
MEMBER_VALUE varchar(100)
)
insert INTO #MEMBER_ATTRIBUTES values(1,1,'My Main Interests','traveling')
insert INTO #MEMBER_ATTRIBUTES values(2,1,'I m Most Likly to be travelling','With a parents')
insert INTO #MEMBER_ATTRIBUTES values(3,2,'My Main Interests','prizedraw')
insert INTO #MEMBER_ATTRIBUTES values(4,2,'I m Most Likly to be travelling','With my wife')
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
SELECT @cols= stuff((
SELECT ', ' +QUOTENAME(MAX(MEMBER_PROPERTY))
FROM #MEMBER_ATTRIBUTES
group by MEMBER_PROPERTY
order by MEMBER_PROPERTY
FOR XML PATH('')), 1, 2, '');
SET @query = 'SELECT MA_ID,MEMBER_ID, ' + @cols + '
from
(
SELECT MA_ID,MEMBER_PROPERTY as [MEMBER_PROPERTY],MEMBER_VALUE as MEMBER_VALUE,
MEMBER_ID as MEMBER_ID FROM #MEMBER_ATTRIBUTES
) x
pivot
(
MAX(MEMBER_VALUE)
for x.MEMBER_PROPERTY in (' + @cols + ')
) p'
execute sp_executesql @query;
SQL - Pivot table - uneven rows per column
select City,
(
select suburb + ' ' from @test1 where city=a.city for xml path(''),
type).value('.[1]', 'varchar(max)'
),
(
select vehicle+ ' ' from @test2 where city=a.city for xml path(''),
type).value('.[1]', 'varchar(max)'
)
from @test0 a
Related Topics
How to Limit the Results on a SQL Query
There Are a Method to Paging Using Ansi SQL Only
How to Return a New Identity Column Value from an SQLserver Select Statement
Difference Between Datetime Converts in Msexcel and SQL Server
Difference Between SQL Connection and Oledb Connection
Entity Framework Indexing All Foreign Key Columns
Temporary Table Record Limit in SQL Server
Find the Date/Time a Table's Column Was Created
Why When Matched' Cannot Appear More Than Once in a 'Update' Clause of a Merge Statement
How to Test My Ad-Hoc SQL with Parameters in Postgres Query Window
Find the Byte Size of a Row in Postgresql
Insert Identity Column Value into Table from Another Table
Calling a Stored Proc Over a Dblink
Help with SQL Server Trigger to Truncate Bad Data Before Insert
Deleting Value Using SQLite While Doing an Inner Join
How to Display Column Headers Returned by Postgresql Function