rewrite my table - SQL Server Forums

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

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

rewrite my table

Author

Topic

harkirat777
Starting Member

2 Posts

Posted - 2014-06-26 : 08:29:21

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

66 Posts

Posted - 2014-06-30 : 07:34:52

harkirat777
Starting Member

2 Posts

Posted - 2014-07-01 : 04:01:47

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.

sunder.bugatha
Yak Posting Veteran

66 Posts

Posted - 2014-07-03 : 01:54:14

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources