Transforming Data in a Query 1

[xlbo]

Hello all,
not great on SQL but I can get by - running into a bit of an issue that I can't seem to get round....

Using MS Reporting Services over a SQL Server database

Data is currently held like:

[tt]
AGM ID | Product Type | Tied Flag
1234 | Ales | Y
1234 | Lager | Y
1234 | Cider | Y
5678 | Ales | Y
5678 | Lager | Y
5678 | Cider | N
[/tt]

What I would like to know is if there is a standard methodology to transform that into:
[tt]
AGM ID | Ales tie | Lager Tie | Cider Tie
1234 | Y | Y | Y
5678 | Y | Y | N
[/tt]

Basically, doing the same as a "Crosstab Query" in MS Access.....

Any help would be appreciated

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[r937]

select AGMID 
     , max(case when ProductType = 'Ales'
                then TiedFlag
             end) as AlesTie
     , max(case when ProductType = 'Lager' 
                then TiedFlag
             end) as LagerTie
     , max(case when ProductType = 'Cider' 
                then TiedFlag
             end) as CiderTie
  from yourtable
group 
    by AGMID

btw, what's Reporting Services? does it support regular SQL?

r937.com | rudy.ca

 

[xlbo]

Cheers r937 - I will give that a go

Reporting Services is a BI tool from Microsoft which is being bundled with SQL Server. AFAIK, it supports regular (MS) SQL


Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[xlbo]

Thank you r937 - That seems to work nicely - have a star

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question