[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 378
  • Last Modified:

Need help in writing Script with the given cindition

I have the table value like below and need to write script to get the below required format result. Please send me the script. thanks.
Table:            
Reportdate      Name      mark
1/1/2011      XXX      10
1/2/2011      XXX      5
1/1/2011      yyy      25
1/2/2011      yyy      60

Required result            
NAME      1/1/2011      1/2/2011
XXX      10      5
YYY      25      60
0
PKTG
Asked:
PKTG
3 Solutions
 
SharathData EngineerCommented:
try this.
select * 
  from your_table
 pivot (max(mark) for ReportDate in ([1/1/2011],[1/2/2011])) as p

Open in new window

0
 
PKTGAuthor Commented:
It is not working and i have many reportdate value like i need to give the report for 1 or 2 month.
when i tried the above script mark values are showing Null.
0
 
RimvisCommented:
Hi PKTG,

try this:
--Generate date list
DECLARE @sDates VARCHAR(MAX)

SET @sDates = (SELECT DISTINCT '[' + CONVERT(VARCHAR(50), Reportdate, 101) + '],' FROM YourTable ORDER BY 1 FOR XML PATH(''))
SET @sDates = LEFT(@sDates, LEN(@sDates)- 1)
--SELECT @sDates

---Generate dynamic PIVOT
DECLARE @sSQL NVARCHAR(MAX)

SET @sSQL = 'SELECT pvt.[Name], ' + @sDates + ' FROM (SELECT Reportdate, [Name], MARK FROM YourTable) AS t 
				PIVOT (SUM(MARK) FOR Reportdate IN (' + @sDates + ')) AS pvt ORDER BY 1'
EXEC (@sSQL)				

Open in new window

0
Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

 
SharathData EngineerCommented:
Can you post the data which is not working.I checked with your data and got the marks fine.
declare @table table(Reportdate date,Name varchar(3),mark int)
insert @table values('1/1/2011','XXX',10)
insert @table values('1/2/2011','XXX',5)
insert @table values('1/1/2011','yyy',25)
insert @table values('1/2/2011','yyy',60)
select * 
  from @table
 pivot (max(mark) for ReportDate in ([1/1/2011],[1/2/2011])) as p
/*
Name	1/1/2011	1/2/2011
XXX	10	5
yyy	25	60
*/

Open in new window

0
 
John ClaesSenior .Net Consultant & Technical AnalistCommented:
because you're having a dynamical set of Columns in your pivot you should do something like this

 
DECLARE @columns VARCHAR(8000)

SELECT @columns = COALESCE(@columns + ',[' + convert(nvarchar(8),reportdate,112) + ']',
'[' + convert(nvarchar(8),reportdate,112)+ ']')
FROM #tmp
GROUP BY reportdate

DECLARE @query VARCHAR(8000)
SET @query = '
SELECT *
FROM #tmp
PIVOT
(
MAX(mark)
FOR reportdate
IN (' + @columns + ')
)
AS p'

exec (@query)

Open in new window



example
 
create table #tmp
(
reportdate datetime,
[Name] nvarchar(20),
mark int
)

insert into #tmp select '20110101','XXX',10
insert into #tmp select '20110201','XXX',05
insert into #tmp select '20110101','YYY',25
insert into #tmp select '20110201','YYY',60


DECLARE @columns VARCHAR(8000)

SELECT @columns = COALESCE(@columns + ',[' + convert(nvarchar(8),reportdate,112) + ']',
'[' + convert(nvarchar(8),reportdate,112)+ ']')
FROM #tmp
GROUP BY reportdate

DECLARE @query VARCHAR(8000)
SET @query = '
SELECT *
FROM #tmp
PIVOT
(
MAX(mark)
FOR reportdate
IN (' + @columns + ')
)
AS p'

exec (@query)

drop table #tmp

Open in new window



source : http://www.tsqltutorials.com/pivot.php
Part : Dynamic PIVOT - PIVOT without specifying columns

regards

poor beggar
0
 
LowfatspreadCommented:
It is not working and i have many reportdate value like i need to give the report for 1 or 2 month.
when i tried the above script mark values are showing Null.


so explain what the report is trying to show...

ie 1 want a daily report covering the last 2 months...
or i have weekly figures and want a report for the last 2 months...

are the row values to be discrete or need aggregation for the period...

do the date include a time component?
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now