Menu
In one of my tables i store my advertisement data, thats one row per advertisement. I also store some dates in an other table, but that's one row per date because i don't know howmany dates a specific advertisement gets. I want to select al the dates (where ID adventisement = 1) in the same query as the data selection, seperated bij a komma. Only problem is that i get as many rows as there are dates, i only want one row with al the data…..
Table 1 (Advertisements) ID_adv data 1 data2 1 name1 picture1 2 name2 picture2 3 name3 picture3 4 name4 picture4 Table 2 (Dates) ID ID_adv date 1 2 1-1-2012 2 2 2-1-2012 3 3 1-1-2012 4 3 2-1-2012 5 3 3-1-2012 6 3 4-1-2012 Outcome query (Select ID_adv, data1, data2, dates WHERE ID_adv = 3)
3,name3,picture3,"1-1-2012,2-1-2012,3-1-2012,4-1-2012" The dates column can be one string with al the dates seperated by a comma….
Any ideas?
605
You can concatenate rows into single string using COALESCE method. This COALESCE method can be used in SQL Server version 2008 and higher. All you have to do is, declare a varchar variable and inside the coalesce, concat the variable with comma and the column, then assign the COALESCE to the variable.
You may use the IN, ANY, or ALL operator in outer query to handle a subquery that returns multiple rows. Contents: Using IN operator with a Multiple Row Subquery. Using NOT IN operator with a Multiple Row Subquery.
In this case, we use GROUP_CONCAT function to concatenate multiple rows into one column. GROUP_CONCAT concatenates all non-null values in a group and returns them as a single string. If you want to avoid duplicates, you can also add DISTINCT in your query.
To merge two or more rows into one, here's what you need to do: Select the range of cells where you want to merge rows. Go to the Ablebits Data tab > Merge group, click the Merge Cells arrow, and then click Merge Rows into One.
You can use GROUP_CONCAT() and GROUP BY to get the results you desire:
SELECT t1.*, GROUP_CONCAT(t2.date) AS dates FROM Table1 t1 LEFT JOIN Table2 t2 ON t2.ID_adv = t1.ID_adv GROUP BY t1.ID_adv This returns all the dates for each advertisement, concatenated by commas. Where there are no dates in Table2 for a particular advertisment, you'll get NULL for the dates column.
To target a particular advertisement, simply add the WHERE clause:
SELECT t1.*, GROUP_CONCAT(t2.date) AS dates FROM Table1 t1 LEFT JOIN Table2 t2 ON t2.ID_adv = t1.ID_adv WHERE t1.ID_adv = 3 GROUP BY t1.ID_adv You can turn rows into a column with GROUP_CONCAT function
32
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
