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 @ 12:24 AM
Points: 5,317, Visits: 12,355
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.

It is better to keep your mouth shut and appear stupid than to open it and remove all doubt. (Mark Twain)
Post #1598576
Posted Friday, August 1, 2014 12:06 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, December 19, 2014 3:33 AM
Points: 856, Visits: 1,506
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 Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Today @ 6:33 AM
Points: 91, Visits: 100
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