i****a
发帖数: 36252 | 1 have a table in the following data:
ContactID____Type____Value
1**********************[email protected]
123_________phone___555-555-5555
123_________fax_____888-888-8888
how do I do a select statment to get it into 1 result line like this:
ContactID, ContactEmail, ContactPhone, ContactFax
123, a*[email protected], 555-555-5555, 888-888-8888
I am using
select
case type
when 'email' then Value AS ContactEmail
end,
case type
when 'phone' then Value AS ContactPhone
end,
case type
when 'fax' then Value AS ContactFax | B*****g
发帖数: 34098 | 2 打到google。
I perfer the xml one.
http://www.projectdmx.com/tsql/rowconcatenate.aspx
【在 i****a 的大作中提到】
: have a table in the following data:
: ContactID____Type____Value
: 1**********************[email protected]
: 123_________phone___555-555-5555
: 123_________fax_____888-888-8888
: how do I do a select statment to get it into 1 result line like this:
: ContactID, ContactEmail, ContactPhone, ContactFax
: 123, a*[email protected], 555-555-5555, 888-888-8888
: I am using
: select
| j*****n
发帖数: 1781 | 3 Use PIVOT in SQL Server 2005 or upper versions...
;WITH VTable (ContactID, [Type], [Value])
AS
(
SELECT 123, 'email', 'a*[email protected]'
UNION ALL
SELECT 123,'phone', '555-555-5555'
UNION ALL
SELECT 123,'fax', '888-888-8888'
UNION ALL
SELECT 456, 'email', 'c*[email protected]'
UNION ALL
SELECT 456,'phone', '444-555-5555'
UNION ALL
SELECT 456,'fax', '444-888-8888'
)
SELECT ContactID, [email] AS Email, [phone] AS Phone, [fax] AS Fax
FROM ( SELECT ContactID, [Type], [Value]
FROM VTable ) P
PIVOT
( MAX([
【在 B*****g 的大作中提到】
: 打到google。
: I perfer the xml one.
: http://www.projectdmx.com/tsql/rowconcatenate.aspx
| c*****y
发帖数: 75 | 4 SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Play_test](
[ContactID] [varchar](50) NULL,
[type] [varchar](50) NULL,
[Value] [varchar](50) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF | c*****y
发帖数: 75 | 5 ContactID email
fax
phone | c*****y
发帖数: 75 | 6 2 lou, 3 lou shi da niu! | i****a
发帖数: 36252 | 7 indeed thank you big cows
I googled and read the projectdmx article, just didn't understand how to
apply it so I can get seperate columns. | i****a
发帖数: 36252 | 8 that's a smart way of using case and max. thx
【在 c*****y 的大作中提到】
: SET ANSI_NULLS ON
: GO
: SET QUOTED_IDENTIFIER ON
: GO
: SET ANSI_PADDING ON
: GO
: CREATE TABLE [dbo].[Play_test](
: [ContactID] [varchar](50) NULL,
: [type] [varchar](50) NULL,
: [Value] [varchar](50) NULL
|
|
