联接的SQL查询并将列转换为csv [英] Sql Query for Join and convert Columns to csv

问题描述

我在下面列出了两个名为tbl_Products和tbl_Keywords的表

Hi,

I have Two table Named tbl_Products and tbl_Keywords listed below

tbl_Products 
ID 	KeywordIds
1 	1,2
2 	1,3
3 	1,4
4 	2,4

tbl_Keywords 
Key_ID	Keyword
1	Keyword1
2	Keyword2
3	Keyword3
4	Keyword4

我想将tbl_Products与tbl_keywords以及csv作为关键字一起加入，如下所示

I want to join this tbl_Products with tbl_keywords with keywords as csv as show below

Result
ID 	Keywords	keywordCSV
1 	1,2		Keyword1,Keyword2
2 	1,3		Keyword1,Keyword3
3 	1,4		Keyword1,Keyword4
4 	2,4		Keyword2,Keyword4

谢谢所有

Thanks All

推荐答案

您的表设计看起来不正确(如果您使用的是关系数据库).
第一个表tbl_Products的格式不是第一个正常形式.

桌子应该像

Your table design looks incorrect (if you are using relational databases).
The first table tbl_Products is not in first normal form.

The table should like

ID  KeywordIds
1   1
1   2
2   1
2   3
3   1
3   4
4   2
4   4

如果是这样，那么简单的联接就会为您提供所需的结果(或与之类似的结果).

If it did, a simple join would give you the results (or similar results to what) you are looking for.

如解决方案1所述，您的数据库设计不正确，我认为它是现有数据库，您无法更改数据库结构

这是查询

As mentioned in the solution 1, your database design is incorrect and I assume that it is an exisiting database and you are unable to change the database structure

This is the query

with testdata (ProductID, ItemID, Keywords) as (

select tbl_Products.ID,b.items,tbl_Keywords.Keywords
from tbl_Products
cross apply dbo.Split(tbl_Products.KeywordIDs ,',') b
left join tbl_Keywords on b.items=tbl_Keywords.ID
) select distinct ProductID ,substring( (
            select  ','+ Keywords
            from testdata inner1
            where inner1.ProductID =outer1.ProductID  for XML path('')
                  ),2,500)keywordCSV
from testdata outer1

并且您需要在执行查询之前在数据库中创建以下函数

and you needs to have the following function created in your database prior to the query execution

Create FUNCTION [dbo].[Split] (@String varchar(8000), @Delimiter char(1))
   returns @temptable TABLE (items varchar(8000))
   as
   begin
       declare @idx int
        declare @slice varchar(8000)

        select @idx = 1
            if len(@String)<1 or @String is null  return

       while @idx!= 0
       begin
           set @idx = charindex(@Delimiter,@String)
           if @idx!=0
               set @slice = left(@String,@idx - 1)
           else
              set @slice = @String

           if(len(@slice)>0)
               insert into @temptable(Items) values(@slice)

           set @String = right(@String,len(@String) - @idx)
           if len(@String) = 0 break
       end
   return
   end

GO

我已经在SQL Server 2008中测试了该解决方案，并且可以使用，如果您有任何问题，请让我知道错误消息以及您的SQL Server版本.

I have tested the solution in SQL server 2008 and it works, if you have any issues let me know the error message along with your SQL server version.

我有一个解决方案

I got a solution

declare @tbl_Products table(ID int, Keywords varchar(10))

insert into @tbl_Products values
(1,   '1,2'),
(2,   '1,3'),
(3,   '1,4'),
(4,   '2,4')

declare @tbl_Keywords table(Key_ID int, Keyword varchar(10))

insert into @tbl_Keywords values
(1,        'Keyword1'),
(2,        'Keyword2'),
(3,        'Keyword3'),
(4,        'Keyword4')

select P.ID,
       P.Keywords,
       stuff((select ', '+K.keyword
              from @tbl_Keywords as K
                inner join P.XKeywords.nodes('/k') as KX(N)            
                  on KX.N.value('.', 'int') = K.Key_ID
              for xml path(''), type).value('.', 'varchar(max)'), 1, 2, '') as keywordCSV
from (
      select ID,
             Keywords,
             cast('<k>'+replace(Keywords, ',', '</k><k>')+'</k>' as xml) as XKeywords
      from @tbl_Products
     ) as P​

谢谢大家的答复.

Thanks All for Reply.

这篇关于联接的SQL查询并将列转换为csv的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！