将多行中的列合并为单行 [英] Combine column from multiple rows into single row
问题描述
我的数据如下
数据如下:
位置用户
------------------------------------------- ------------
美国Jhon,Ram
日本Kevin,Kenady
印度Michal,Joy,Selvin ,Mohan
我想要外出光顾
用户
- -----------------------------
Jhon,Ram,Kevin,Kenady,Michal,Joy,Selvin ,Mohan
请让我知道如何处理这个。
感谢advacne
使用此代码
< pre lang =sql> 声明 @ tbl1 表(location varchar ( 50 ),用户 varchar ( 100 ))
插入 进入 @ tbl1
选择 ' USA',' Jhon,Ram'
union
选择 ' 日本',' Kevin,Kenady'
union
选择 ' India',' Michal,Joy,Selvin,Mohan'
SELECT STUFF(( SELECT ' ,' + Users FROM @ tbl1 nolock 订单 由用户用于 XML路径(' ')), 1 , 1 ,' ') as 用户
HI,
您可以使用以下链接进行操作。请仔细阅读。
1. 将行数据转换为SQL Server中的列
2. SQL Server将列转换为行
3. 在SQL中将行转换为列
一旦能够在行中获取列数据,然后将它们连接起来这将为您产生结果。 :D
谢谢
试试这样的事情
string total = string.Empty;
DataTable table = new DataTable();
table.Columns.Add(Location,typeof(string));
table.Columns.Add(Users,typeof(string));
table.Rows.Add (USA,Jhon,Ram);
table.Rows.Add(Japan,Kevin,Kenady);
table.Rows.Add (印度,Michal,Joy,Selvin,Mohan);
String str =;
table.Rows.Cast< datarow>()。ToList ()。ForEach((datarow)=> {str + = datarow [Users]。ToString()+,;});
str = str.TrimEnd(',');
I have the data like below
The data looks like this:
Location Users
-------------------------------------------------------
USA Jhon, Ram
Japan Kevin, Kenady
India Michal, Joy, Selvin, Mohan
I want the out put as
Users
------------------------------
Jhon, Ram, Kevin, Kenady, Michal, Joy, Selvin, Mohan
Please let me know how can i proceed with this.
Thanks in advacne
Use this code
declare @tbl1 Table (location varchar(50), Users varchar(100)) insert into @tbl1 select 'USA', 'Jhon, Ram' union select 'Japan', 'Kevin, Kenady' union select 'India', 'Michal, Joy, Selvin, Mohan' SELECT STUFF((SELECT ','+ Users FROM @tbl1 nolock order by users for XML path('') ),1,1,'') as Users
HI,
You can do it using following link. Please go through them.
1. Convert row data to column in SQL Server
2. SQL Server convert columns to rows
3. Convert rows to columns in SQL
Once you can able to get the column data in rows then concatenate them and this will produce the result for you. :D
Thanks
Try something like this
string total = string.Empty;
DataTable table = new DataTable();
table.Columns.Add("Location", typeof(string));
table.Columns.Add("Users", typeof(string));
table.Rows.Add("USA","Jhon, Ram");
table.Rows.Add("Japan", "Kevin, Kenady");
table.Rows.Add("India", "Michal, Joy, Selvin, Mohan");
String str = "";
table.Rows.Cast<datarow>().ToList().ForEach((datarow) => { str += datarow["Users"].ToString() + ","; });
str = str.TrimEnd(',');
这篇关于将多行中的列合并为单行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!