SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 rewrite my table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

harkirat777
Starting Member

2 Posts

Posted - 06/26/2014 :  08:29:21  Show Profile  Reply with Quote
I want to rewrite my table which look likes:

TestName | Date | Result
---------|----------|-------
Test 1 |26/03/2014| Good
Test 2 |26/03/2014| Not Good
Test 3 |26/03/2014| Good
Test 1 |25/03/2014| Good
Test 2 |25/03/2014| Not Good
Test 3 |25/03/2014| Good
Test 1 |19/03/2014| Good
Test 2 |19/03/2014| Not Good
Test 3 |19/03/2014| Good
Test 1 |17/03/2014| Good
Test 2 |17/03/2014| Not Good
Test 3 |17/03/2014| Good


I want to rewrite as below

TestName | Week1 results | Week2 Results | ....and so on for 1 week per page.
Test 1 | Good | Good |...............
Test 2 | Not Good | Not Good |...............
Test 3 | Good | Good |...............

sunder.bugatha
Starting Member

India
22 Posts

Posted - 06/30/2014 :  07:34:52  Show Profile  Click to see sunder.bugatha's MSN Messenger address  Send sunder.bugatha a Yahoo! Message  Reply with Quote
Are u looking for a query to get the below output?


TestName | Week1 results | Week2 Results | ....and so on for 1 week per page.
Test 1 | Good | Good |...............
Test 2 | Not Good | Not Good |...............
Test 3 | Good | Good |...............

Hema Sunder
Go to Top of Page

harkirat777
Starting Member

2 Posts

Posted - 07/01/2014 :  04:01:47  Show Profile  Reply with Quote
quote:
Originally posted by sunder.bugatha

Are u looking for a query to get the below output?


TestName | Week1 results | Week2 Results | ....and so on for 1 week per page.
Test 1 | Good | Good |...............
Test 2 | Not Good | Not Good |...............
Test 3 | Good | Good |...............

Hema Sunder



Thanks for your reply hema.
I have a front end webpage. So I am looking for query to get that table output.
Go to Top of Page

sunder.bugatha
Starting Member

India
22 Posts

Posted - 07/03/2014 :  01:54:14  Show Profile  Click to see sunder.bugatha's MSN Messenger address  Send sunder.bugatha a Yahoo! Message  Reply with Quote
I have considered only week1 and week2 in the query. Can you check and see if this helps?


declare @test table
(id int identity(1,1) , testname varchar(20))


declare @test_output table
(Testname varchar(20), Week1_results varchar(20),Week2_results varchar(20))
declare @i int = 1;

with CTE (testname,Week1_Results,Week2_Results)
as (
select distinct testname, 'Week1 Results' =
case when test_date between '2014-03-17' and '2014-03-23' then Result END,
'Week2 Results' = case when test_date between '2014-03-24' and '2014-03-31' then Result
END
from test
group by testname,test_date,result)

insert into @test
select distinct testname from CTE

while (@i < = (select max(id) from @test))
begin

with CTE (testname,Week1_Results,Week2_Results)
as (
select distinct testname, 'Week1 Results' =
case when test_date between '2014-03-17' and '2014-03-23' then Result END,
'Week2 Results' = case when test_date between '2014-03-24' and '2014-03-31' then Result
END
from test
group by testname,test_date,result)

insert into @test_output
select (select testname from @test where id=@i) TestName, (select week1_results from CTE where testname= (select testname from @test where id=@i) and week2_results is null) as Week1_results,
(select week2_results from CTE where testname= (select testname from @test where id=@i) and week1_results is null) Week2_results


set @i = @i+1
end

select * from @test_output








Hema Sunder
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000