Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

select statement Expand / Collapse
Author
Message
Posted Thursday, July 31, 2014 10:41 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 5, 2014 10:01 AM
Points: 1, Visits: 14
hi,

I need help to write a select statement that take the upper table and select the lower table.
thanks

Post #1598570
Posted Thursday, July 31, 2014 11:46 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:10 PM
Points: 5,103, Visits: 11,906
PIVOT should get you what you need. Have a look at this example and see whether it helps you.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1598576
Posted Friday, August 1, 2014 12:06 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Yesterday @ 12:16 AM
Points: 752, Visits: 1,323
Following is another way of doing this

Declare @tblPersonTask Table 
(
DayNm char(3),
Person varchar(20),
Task smallint
)

insert into @tblPersonTask
select 'sun', 'Jhon' , 1 union all
select 'sun', 'Smith' , 1 union all
select 'sun', 'Dan' , 2 union all
select 'mon', 'Smith' , 2 union all
select 'mon', 'Jhon' , 3 union all
select 'tue', 'Jhon' , 2 union all
select 'tue', 'Smith' , 3 union all
select 'tue', 'Dan' , 1 union all
select 'wed', 'Smith' , 1 union all
select 'thu', 'Jhon' , 2 union all
select 'fri', 'Jhon' , 2 union all
select 'sat', 'Smith' , 3


select Person,
max(Case when DayNm = 'sun' then Task end) AS 'sun',
max(Case when DayNm = 'mon' then Task end) AS 'mon',
max(Case when DayNm = 'tue' then Task end) AS 'tue',
max(Case when DayNm = 'wed' then Task end) AS 'wed',
max(Case when DayNm = 'thu' then Task end) AS 'thu',
max(Case when DayNm = 'fri' then Task end) AS 'fri',
max(Case when DayNm = 'sat' then Task end) AS 'sat'
from @tblPersonTask
group by Person


Post #1598580
Posted Friday, August 1, 2014 2:04 PM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, September 22, 2014 12:04 AM
Points: 33, Visits: 58
Hi

Please find the needed Pivot query below

Create Table #tblPersonTask 
(
DayNm char(3),
Person varchar(20),
Task smallint
)

insert into #tblPersonTask
select 'sun', 'Jhon' , 1 union all
select 'sun', 'Smith' , 1 union all
select 'sun', 'Dan' , 2 union all
select 'mon', 'Smith' , 2 union all
select 'mon', 'Jhon' , 3 union all
select 'tue', 'Jhon' , 2 union all
select 'tue', 'Smith' , 3 union all
select 'tue', 'Dan' , 1 union all
select 'wed', 'Smith' , 1 union all
select 'thu', 'Jhon' , 2 union all
select 'fri', 'Jhon' , 2 union all
select 'sat', 'Smith' , 3

-- Pivot table with three row and eight columns
SELECT Person , [sun],[mon], [tue],[wed], [thu],[fri], [sat]
FROM
(
-- Select All the Columns needed in the above select statement as well as the pivot
SELECT Person ,Task,DayNm
FROM #tblPersonTask) AS SourceTable
PIVOT
-- Pivot the values (before the FOR statement) for the columns in ()
(
sum(Task)
FOR DayNm IN ([sun],[mon], [tue],[wed], [thu],[fri], [sat])
) AS PivotTable
order by Person

Please also have a look at this nice explanation about Pivot tables.
http://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx

Please feel free to ask if you do not understand the code above.


--------------------------------------------------------------------------------------------------------------------------------------------------------
To some it may look best but to others it might be the worst, but then again to some it might be the worst but to others the best.
Post #1598809
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse