j*****n
发帖数: 1781 | 1 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([ |
|
|
|
|
|
|
|
|
|
|
|
|
|
i****a
发帖数: 36252 | 14 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 |
|
c*****y
发帖数: 75 | 15 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 | 16 ContactID email
fax
phone |
|
|
