在sql join中基于相同的列值添加行值. [英] add row value based on same column value in sql join.

问题描述

我对于不同的trgtVariableNAme列值具有相同的LineItemID列值，我想使用，"为同一lineItemID值添加trgtVariableNAme行值.

i have same LineItemID Column value for diffrent trgtVariableNAme column Value i want to add trgtVariableNAme Row value using '','' for the same lineItemID value

LineItemID   trgtVariableNAme  
1               a
1               b
2               c

加入后

after join

marketOrderID orderID LineNumber pageGroupName trgtVariableName endDate
1               2        1          abc            a              8/09/11
1               2        1          abc            b            8/09/11        
2               3        4           bcv           c             8/09/11

我想要这样

I want like this

marketOrderID orderID LineNumber pageGroupName trgtVariableName endDate
1               2        1          abc            a,b              8/09/11
2               3        4           bcv           c             8/09/11

如何在join或其他方法下进行修改?

how to modify below join or some other method?

SELECT c.marketOrderID, c.orderID,
  d.LineNumber, d.MarketPageGroupCode, t.pageGroupName,
  trgt.trgtVariableName, c.endDate
FROM tblMarketOrder c
INNER JOIN tblMarketOrderLineItem d
  ON c.marketOrderID = d.marketOrderID
INNER JOIN lkpAdsTargetPageGroup t
  ON t.pageGroupID = d.pageGroupID
INNER JOIN tblMarketOrderLineItemsTarget linetarget
  ON d.lineItemID = linetarget.lineItemID
INNER JOIN lkpTargetingVariable trgt
  ON linetarget.trgtVariableID = trgt.trgtVariableID

推荐答案

聚合级联""这不是一个容易的问题.

我为这件事加了一条有趣的文章...在这里
http://www.simple-talk.com /sql/t-sql-programming/concatenating-row-values-in-transact-sql/ [

"Aggregate concatenation" it''s not an easy issue.

I bookmarked an interesting article about it... here it is
http://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/[^]

创建一个接受lineitemid的函数并返回逗号分隔的trgtVariableNAme.

下面的代码可以在函数中使用

Create a funtion which accept the lineitemid and return trgtVariableNAme with comma seperated.

Below code can be used in function

CREATE FUNCTION dbo.udf_GetCommaSepName (@LineItemID INT)
RETURNS VARCHAR(MAX)
BEGIN
DECLARE @Val VARCHAR(MAX)
SET @Val = ''
SELECT @Val = @Val + trgtVariableNAme + ',' FROM lkpTargetingVariable WHERE LineItemID = @LineItemID
RETURN LEFT(@Val,LEN(@Val)-1) --this will remove the comma from last.
END

--Use this function in you SQL Qeury like below...
SELECT c.marketOrderID, c.orderID,
  d.LineNumber, d.MarketPageGroupCode, t.pageGroupName,
  udf_GetCommaSepName(trgt.lineItemID) As trgtVariableName, c.endDate
FROM tblMarketOrder c
INNER JOIN tblMarketOrderLineItem d
  ON c.marketOrderID = d.marketOrderID
INNER JOIN lkpAdsTargetPageGroup t
  ON t.pageGroupID = d.pageGroupID
INNER JOIN tblMarketOrderLineItemsTarget linetarget
  ON d.lineItemID = linetarget.lineItemID
INNER JOIN lkpTargetingVariable trgt
  ON linetarget.trgtVariableID = trgt.trgtVariableID

我希望这能解决您的问题...

谢谢

I hope this resolve ur problem...

Thanks

这篇关于在sql join中基于相同的列值添加行值.的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！