T-SQL Row Concatenate with a Twist?? - 未名空间MITBBS历史存档

国际科技财经博客移民网络热点娱乐民生时事公众号

Redian新闻

>未名空间

>Database - 数据库

T-SQL Row Concatenate with a Twist??

T-SQL Row Concatenate with a Twist??# Database - 数据库

i*a2010-01-26 08:01

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*g2010-01-26 08:01

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*n2010-01-26 08:01

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*y2010-01-26 08:01

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*y2010-01-26 08:01

5 楼

ContactID email
fax
phone

c*y2010-01-26 08:01

6 楼

2 lou, 3 lou shi da niu!

i*a2010-01-26 08:01

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*a2010-01-26 08:01

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