Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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
Yak Posting Veteran

India
66 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
Yak Posting Veteran

India
66 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  
 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.09 seconds. Powered By: Snitz Forums 2000