I have a table T1 like this:
NameID | F1 | V1 | F2 | V2 |
---
1 A 49% B 51%
2 C 35% D 65%
3 E 20% B 80%
4 D 10% A 90%
I need a query to transform T1 into a recordset like this:
NameID | A | B | C | D | E |
---
1 49% 51%
2 35% 65%
3 80% 20%
4 90% 10%
Thanks,
GBThis is a multi-part message in MIME format.
--=_NextPart_000_0176_01C3AF6F.30174D50
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: 7bit
Try:
select
NameID
, max (case when F = 'A' then Value end) as A
, max (case when F = 'B' then Value end) as B
, max (case when F = 'C' then Value end) as C
, max (case when F = 'D' then Value end) as D
, max (case when F = 'E' then Value end) as E
from
(
select
NameID
, case type 1 then F1 else F2 end as F
, case type 1 then V1 else V2 end as Value
from
T1
cross join
(
select 1 as type union all
select 2
) as x
) as y
group by
NameID
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"GB" <v7v1k3@.hotmail.com> wrote in message
news:Y78vb.67322$1K.40554@.edtnps84...
Hello:
I have a table T1 like this:
NameID | F1 | V1 | F2 | V2 |
---
1 A 49% B 51%
2 C 35% D 65%
3 E 20% B 80%
4 D 10% A 90%
I need a query to transform T1 into a recordset like this:
NameID | A | B | C | D | E |
---
1 49% 51%
2 35% 65%
3 80% 20%
4 90% 10%
Thanks,
GB
--=_NextPart_000_0176_01C3AF6F.30174D50
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Try:
select
NameID
, max (case =when F =3D 'A' then Value end) as A
, max (case =when F =3D 'B' then Value end) as B
, max (case =when F =3D 'C' then Value end) as C
, max (case =when F =3D 'D' then Value end) as D
, max (case =when F =3D 'E' then Value end) as Efrom
(
select
NameID
, case type 1 =then F1 else F2 end as F
, case type 1 =then V1 else V2 end as Value
from
=T1
cross join
(
select 1 as =type union all
select =2
) as x
) as y
group by
=NameID
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"GB"
--=_NextPart_000_0176_01C3AF6F.30174D50--|||This is a multi-part message in MIME format.
--=_NextPart_000_004D_01C3AF59.24E0FAA0
Content-Type: text/plain;
charset="koi8-r"
Content-Transfer-Encoding: quoted-printable
Hello, Tom:
Thank you for the reply, but when I try to execute this statement
I'v got the errors:
Server: Msg 170, Level 15, State 1, Line 12
Line 12: Incorrect syntax near '1'.
Server: Msg 156, Level 15, State 1, Line 20
Incorrect syntax near the keyword 'as'.
How to fix the problem?
Thanks,
GB
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:euMUdk5rDHA.1184@.TK2MSFTNGP10.phx.gbl...
Try:
select
NameID
, max (case when F =3D 'A' then Value end) as A
, max (case when F =3D 'B' then Value end) as B
, max (case when F =3D 'C' then Value end) as C
, max (case when F =3D 'D' then Value end) as D
, max (case when F =3D 'E' then Value end) as E
from
(
select
NameID
, case type 1 then F1 else F2 end as F
, case type 1 then V1 else V2 end as Value
from
T1
cross join
(
select 1 as type union all
select 2
) as x
) as y
group by
NameID
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"GB" <v7v1k3@.hotmail.com> wrote in message =news:Y78vb.67322$1K.40554@.edtnps84...
Hello:
I have a table T1 like this:
NameID | F1 | V1 | F2 | V2 |
---
1 A 49% B 51%
2 C 35% D 65%
3 E 20% B 80%
4 D 10% A 90%
I need a query to transform T1 into a recordset like this:
NameID | A | B | C | D | E |
---
1 49% 51%
2 35% 65%
3 80% 20%
4 90% 10%
Thanks,
GB
--=_NextPart_000_004D_01C3AF59.24E0FAA0
Content-Type: text/html;
charset="koi8-r"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Hello, Tom:
Thank you for the reply, but when I try to execute =this statement
I'v got the errors:
Server: Msg 170, Level 15, State 1, Line 12Line =12: Incorrect syntax near '1'.Server: Msg 156, Level 15, State 1, Line 20Incorrect syntax near the keyword 'as'.
How to fix the problem?
Thanks,
GB
"Tom Moreau"
Try:
select
NameID
, max (case =when F =3D 'A' then Value end) as A
, max (case =when F =3D 'B' then Value end) as B
, max (case =when F =3D 'C' then Value end) as C
, max (case =when F =3D 'D' then Value end) as D
, max (case =when F =3D 'E' then Value end) as Efrom
(
select
NameID
, case type =1 then F1 else F2 end as F
, case type =1 then V1 else V2 end as Value
from
=T1
cross join
(
select 1 =as type union all
select =2
) as x
) as y
group by
=NameID
-- Tom
=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"GB"
--=_NextPart_000_004D_01C3AF59.24E0FAA0--|||This is a multi-part message in MIME format.
--=_NextPart_000_0064_01C3AF5D.1EB3E030
Content-Type: text/plain;
charset="koi8-r"
Content-Transfer-Encoding: quoted-printable
I'v fixed the prblem:
select
NameID
, max (case when F =3D 'A' then Value end) as A
, max (case when F =3D 'B' then Value end) as B
, max (case when F =3D 'C' then Value end) as C
, max (case when F =3D 'D' then Value end) as D
, max (case when F =3D 'E' then Value end) as E
from
(
select
NameID
, (case when type =3D 1 then F1 else F2 end) as F
, (case when type =3D 1 then V1 else V2 end) as Value
from
T1
cross join
(
select 1 as type union all
select 2
) as x
) as y
group by
NameID
Thanks,
GB
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:euMUdk5rDHA.1184@.TK2MSFTNGP10.phx.gbl...
Try:
select
NameID
, max (case when F =3D 'A' then Value end) as A
, max (case when F =3D 'B' then Value end) as B
, max (case when F =3D 'C' then Value end) as C
, max (case when F =3D 'D' then Value end) as D
, max (case when F =3D 'E' then Value end) as E
from
(
select
NameID
, case type 1 then F1 else F2 end as F
, case type 1 then V1 else V2 end as Value
from
T1
cross join
(
select 1 as type union all
select 2
) as x
) as y
group by
NameID
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"GB" <v7v1k3@.hotmail.com> wrote in message =news:Y78vb.67322$1K.40554@.edtnps84...
Hello:
I have a table T1 like this:
NameID | F1 | V1 | F2 | V2 |
---
1 A 49% B 51%
2 C 35% D 65%
3 E 20% B 80%
4 D 10% A 90%
I need a query to transform T1 into a recordset like this:
NameID | A | B | C | D | E |
---
1 49% 51%
2 35% 65%
3 80% 20%
4 90% 10%
Thanks,
GB
--=_NextPart_000_0064_01C3AF5D.1EB3E030
Content-Type: text/html;
charset="koi8-r"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
I'v fixed the prblem:
select NameID, max (case when F =3D 'A' then Value end) =as A, max (case when F =3D 'B' then Value end) as B, max (case when F =3D 'C' then Value end) as C, max (case when F =3D 'D' then Value end) as D, max (case when F =3D 'E' then Value end) as Efrom(select NameID, (case when type =3D 1 then F1 else =F2 end) as F, (case when type =3D 1 then V1 else V2 end) as Valuefrom T1cross =join( select 1 as type union all select 2) as x) =as ygroup by NameID
Thanks,
GB
"Tom Moreau"
Try:
select
NameID
, max (case =when F =3D 'A' then Value end) as A
, max (case =when F =3D 'B' then Value end) as B
, max (case =when F =3D 'C' then Value end) as C
, max (case =when F =3D 'D' then Value end) as D
, max (case =when F =3D 'E' then Value end) as Efrom
(
select
NameID
, case type =1 then F1 else F2 end as F
, case type =1 then V1 else V2 end as Value
from
=T1
cross join
(
select 1 =as type union all
select =2
) as x
) as y
group by
=NameID
-- Tom
=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"GB"
--=_NextPart_000_0064_01C3AF5D.1EB3E030--|||This is a multi-part message in MIME format.
--=_NextPart_000_0231_01C3AF77.7C2897F0
Content-Type: text/plain;
charset="koi8-r"
Content-Transfer-Encoding: 7bit
Sorry about that. When looks like then after a while ... ;-)
select
NameID
, max (case when F = 'A' then Value end) as A
, max (case when F = 'B' then Value end) as B
, max (case when F = 'C' then Value end) as C
, max (case when F = 'D' then Value end) as D
, max (case when F = 'E' then Value end) as E
from
(
select
NameID
, case type when 1 then F1 else F2 end as F
, case type when 1 then V1 else V2 end as Value
from
T1
cross join
(
select 1 as type union all
select 2
) as x
) as y
group by
NameID
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"GB" <v7v1k3@.hotmail.com> wrote in message
news:sB8vb.67375$1K.38748@.edtnps84...
Hello, Tom:
Thank you for the reply, but when I try to execute this statement
I'v got the errors:
Server: Msg 170, Level 15, State 1, Line 12
Line 12: Incorrect syntax near '1'.
Server: Msg 156, Level 15, State 1, Line 20
Incorrect syntax near the keyword 'as'.
How to fix the problem?
Thanks,
GB
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:euMUdk5rDHA.1184@.TK2MSFTNGP10.phx.gbl...
Try:
select
NameID
, max (case when F = 'A' then Value end) as A
, max (case when F = 'B' then Value end) as B
, max (case when F = 'C' then Value end) as C
, max (case when F = 'D' then Value end) as D
, max (case when F = 'E' then Value end) as E
from
(
select
NameID
, case type 1 then F1 else F2 end as F
, case type 1 then V1 else V2 end as Value
from
T1
cross join
(
select 1 as type union all
select 2
) as x
) as y
group by
NameID
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"GB" <v7v1k3@.hotmail.com> wrote in message
news:Y78vb.67322$1K.40554@.edtnps84...
Hello:
I have a table T1 like this:
NameID | F1 | V1 | F2 | V2 |
---
1 A 49% B 51%
2 C 35% D 65%
3 E 20% B 80%
4 D 10% A 90%
I need a query to transform T1 into a recordset like this:
NameID | A | B | C | D | E |
---
1 49% 51%
2 35% 65%
3 80% 20%
4 90% 10%
Thanks,
GB
--=_NextPart_000_0231_01C3AF77.7C2897F0
Content-Type: text/html;
charset="koi8-r"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Sorry about that. When looks =like then after a while ... ;-)
select
NameID
, max (case =when F =3D 'A' then Value end) as A
, max (case =when F =3D 'B' then Value end) as B
, max (case =when F =3D 'C' then Value end) as C
, max (case =when F =3D 'D' then Value end) as D
, max (case =when F =3D 'E' then Value end) as Efrom
(
select
NameID
, case type =when 1 then F1 else F2 end as F
, case type =when 1 then V1 else V2 end as Value
from
=T1
cross join
(
select 1 as =type union all
select =2
) as x
) as y
group by
NameID
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"GB"
Hello, Tom:
Thank you for the reply, but when I try to execute =this statement
I'v got the errors:
Server: Msg 170, Level 15, State 1, Line 12Line =12: Incorrect syntax near '1'.Server: Msg 156, Level 15, State 1, Line 20Incorrect syntax near the keyword 'as'.
How to fix the problem?
Thanks,
GB
"Tom Moreau"
Try:
select
NameID
, max (case =when F =3D 'A' then Value end) as A
, max (case =when F =3D 'B' then Value end) as B
, max (case =when F =3D 'C' then Value end) as C
, max (case =when F =3D 'D' then Value end) as D
, max (case =when F =3D 'E' then Value end) as Efrom
(
select
NameID
, case type =1 then F1 else F2 end as F
, case type =1 then V1 else V2 end as Value
from
=T1
cross join
(
select 1 =as type union all
select =2
) as x
) as y
group by
=NameID
-- Tom
=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"GB"
--=_NextPart_000_0231_01C3AF77.7C2897F0--
No comments:
Post a Comment