Query der samler tekst fra flere rækker i én celle

It will be possible if there is a field which can make each row unique and also indicate which order the texts are to be concatentated into one field..


You should also follow upp on other open questions you have!

Hey Terry

kolonnen "insp. lot" er entydig. derudover har jeg faktisk en kolonne der hedder "line" som indikerer concatineringsrækkefølgen. værdierne i "line" er 001, 002, 003, osv....


Jeg rydder op i åbne SPM asap.


/henrik

"kolonnen "insp. lot" er entydig" NOT in your example!

I think I need a bit more infromation before I can give a suggestion to an answer.

Are you trying to convert 10 records into a single record?

Which Line number does the record have which is to contain all texts?


The reason why I'm asking is this. If you wanted to convert the records into one then you should use VBA code to convert your data once and for all (permanently)

If you want to keep all recortds and opnly show a single record with the concatenated texts then you should do it with SQL using a number of sub selects.

Ja, jeg prøver at konvertere X records, til 1.

Den record der skal indeholde al tekst har altid "line number" 001

Jeg vil gerne beholde alle records, så det må være SQL vi er ude efter.

Men der er i virkeligheden mange rækker, med forskellige "insp. lot" numre, hvor hvert "insp lot" nummer har en række records med "line number" 001-00X

Alle disse mange "insp. lot" skal have deres tekst samlet under hver deres line 001.


/henrik

001-00X

How shoulod I understand that, is 001 to 010 ?

or is X an unknown value?

x betyder et eller andet tal. Der kan være alt fra 2 til 30 "line number" rækker. X indikerer bare at antallet at rækker varierer fra "insp lot" til "insp lot".

/henrik

You wont be able to do this in a single SQL unless you make a function (VBA).

Hvordan gør jeg det?

I can give you some ideas but I dont think I'll have much time for a while to do it for you.

And if this problem has anything to do with your newest question then I would consider finding a better solution.

I have a feeling that this is going to be too much for you, but here goes anyway.

You need to make a function which openes a recordset with the records with line number = "001" (The ones which are to contain all texts)

Loop through the recordset and for each record you now open another recordset which contains the matching records appart from the one where Line number = "001"
You need to select these sorted on  Line number so that you have them in the correct order.


Now loop through these records appending the text followed by a space to the record inthe first recordset.

Do this for all records in both recordsets.

here comes and answer :o)

and a good weekend too

Ganske kort. Er det noget i stil med nedenstående du refererer til, dog med andre tabeller?




CREATE FUNCTION [dbo].[ufnVendorOnSameLine](@Id int,@Delimiter varchar(10))

RETURNS nvarchar(max)

AS


BEGIN

DECLARE @OnSameLine varchar(max)

set @OnSameLine=''


SELECT @OnSameLine = @OnSameLine + coalesce (Purchasing.Vendor.Name,'')+@Delimiter

FROM  Purchasing.ProductVendor INNER JOIN Purchasing.Vendor

      ON Purchasing.ProductVendor.VendorID = Purchasing.Vendor.VendorID

WHERE (Purchasing.ProductVendor.ProductID = @Id)


set @OnSameLine = substring(@OnSameLine,1,len(@OnSameLine)-1)

RETURN @OnSameLine


END;


SELECT DISTINCT Production.Product.Name AS [Product Name],

                Production.Product.ProductNumber,

                dbo.ufnVendorOnSameLine(ProductVendor.ProductID,', ') AS Vendors

FROM Production.Product INNER JOIN Purchasing.ProductVendor

    ON Production.Product.ProductID = Purchasing.ProductVendor.ProductID

ORDER BY [Product Name]

Det har var tilstrækkeligt.

SELECT [tblData].[ID], Conc("Field1","ID",[ID],"tblData") AS Field1, Conc("Field2","ID",[ID],"tblData") AS Field2
FROM tblData
GROUP BY [tblData].[ID];

vel at mærke sammen med den her:

Public Function Conc(Fieldx, Identity, Value, Source) As Variant
  Dim cnn As ADODB.Connection
  Dim rs As ADODB.Recordset
  Dim SQL As String
  Dim vFld As Variant
 
  Set cnn = CurrentProject.Connection
  Set rs = New ADODB.Recordset
  vFld = Null
 
  SQL = "SELECT [" & Fieldx & "] as Fld" & _
        " FROM [" & Source & "]" & _
        " WHERE [" & Identity & "]=" & Value
 
  ' open recordset.
  rs.Open SQL, cnn, adOpenForwardOnly, adLockReadOnly
 
  ' concatenate the field.
  Do While Not rs.EOF
    If Not IsNull(rs!Fld) Then
      vFld = vFld & ", " & rs!Fld
    End If
    rs.MoveNext
  Loop
  ' remove leading comma and space.
  vFld = Mid(vFld, 3)
 
  Set cnn = Nothing
  Set rs = Nothing

Havent had much time to answer, off on a short holiday tomorrow.

Glad to hear you found a solution.