Selecting multiple columns to one string (by date)

how to combine date and text in excel
excel combine date and time in one cell
how to merge date month and year in excel
combine date and time in excel with am/pm
display two dates in one cell excel
concatenate time and text in excel
excel combining date and time columns
excel combine date and time into timestamp

My pricelist table looks like this:

   ItemCode VendorCode  UnitCost    StartingDate    
   333       362         2.31       2016-08-19 00:00:00.0
   333       362         2.16       2018-02-22 00:00:00.0
   444       362        12.96       2014-01-09 00:00:00.0   
   444       362        13.10       2015-01-09 00:00:00.0
   444       430        13.05       2017-04-01 00:00:00.0
   444       550        13.30       2018-02-01 00:00:00.0

I would like to have query result following:

   333:(362,2.16,2018-02-22)
   444:(362,13.10,2015-01-09),(430,13.05,2017-04-01),(550,13.30,2018-02-01)

So all different Vendors and their prices should be listed and only latest by date. I got this far:

     SELECT 
     Pricelist.ItemCode + ': '+ temp.data 
     FROM Pricelist
         INNER JOIN (SELECT  p1.ItemCode,
         STUFF((SELECT '; ' + p2.VendorCode 
         FROM Pricelist p2
         WHERE p2.ItemCode = p1.ItemCode
         ORDER BY VendorCode 
         FOR XML PATH('')), 1, 1, '') AS data
         FROM Pricelist p1) as temp
     ON Pricelist.ItemCode = temp.ItemCode
     GROUP BY Pricelist.ItemCode, temp.data 
     ORDER BY 1

But not even close to the result I need.


I would use row_number() function :

select concat(itemcode, ':', 
       stuff( ( select top (1) with ties ',(' +concat(VendorCode, ',', UnitCost ,',', cast(StartingDate as date)) +')'
                from Pricelist
                where itemcode = p.itemcode
                order by row_number() over (partition by VendorCode order by StartingDate desc)
                for xml path('')
               ), 1, 1, ''
            ))
from Pricelist p
group by itemcode;

Creating a date column from multiple columns?, Simply, combine all 3 columns into YYYY-MM-DD and then convert this string into Datetime . For example: Select cast(cast(Year as string) +  How to select multiple columns in a pandas dataframe Python is a great language for doing data analysis, primarily because of the fantastic ecosystem of data-centric Python packages. Pandas is one of those packages and makes importing and analyzing data much easier.


Try with this query:

create table #t (ItemCode int, VendorCode int, UnitCost decimal (10,2), StartingDate datetime)
insert into #t values
(333,362, 2.31,'2016-08-19 00:00:00.0'),
(333,362, 2.16,'2018-02-22 00:00:00.0'),
(444,362,12.96,'2014-01-09 00:00:00.0'),  
(444,362,13.10,'2015-01-09 00:00:00.0'),
(444,430,13.05,'2017-04-01 00:00:00.0'),
(444,550,13.30,'2018-02-01 00:00:00.0')

;with tr1 as (
  select
  convert(varchar(100),ItemCode) + ':' as ItemCode, 
    '(' + convert(varchar(100),VendorCode) + ',' + convert(varchar(100),UnitCost) + ',' + convert(varchar(19),StartingDate,121) + ')' as Vals,
  row_number() over (partition by ItemCode,VendorCode order by StartingDate desc) rn
  from #t
  )
select distinct ItemCode,
  stuff((
    select ',' + Vals
    from tr1 b
    where b.ItemCode=tr1.ItemCode
      and rn=1
    for xml path ('')
    ),1,1,'')
from tr1
where rn=1

Combining Data from Separate Columns into a Single Date and , work on combining data from multiple columns in a worksheet in Microsoft Excel. Formula is =CONCATENATE(A2,B2); Select Cell C2; Write =​CONCATENATE( Combining Date and Time Value from separate Columns into a single Cell. This FOR XML PATH method can be used in SQL Server version 2005 and higher. In the below sample query, I’ve concatenated multiple rows of the column “CountryName” to a single string and added a comma between the country names. Then using a substring function, I’m removing the leading comma.


First grouping the itemcodes, and then linking that to a string with the vendor details might be quite performant.

Linking those itemcodes to an OUTER APPLY with a FOR XML works well.

For example :

declare @Pricelist table (ItemCode int, VendorCode int, UnitCost decimal (10,2), StartingDate datetime)

insert into @Pricelist values
(333,362,02.31,'2016-08-19T00:01:00'),
(333,362,02.16,'2018-02-22T00:02:00'),
(444,362,12.96,'2014-01-09T00:03:00'),  
(444,362,13.10,'2015-01-09T00:04:00'),
(444,430,13.05,'2017-04-01T00:05:00'),
(444,550,13.30,'2018-02-01T00:06:00');

select concat(itemcode,':',stuff(x.details,1,1,'')) as ItemVendorDetails
from (select distinct itemcode from @Pricelist) i
outer apply 
(
    select top 1 with ties 
    concat(',(',VendorCode,',',UnitCost,',',convert(date,StartingDate),')')
    from @Pricelist p
    where p.ItemCode = i.ItemCode
    order by row_number() over (partition by ItemCode, VendorCode order by StartingDate desc)
    for xml path('')
) x(details);

Result:

ItemVendorDetails
------------------------------------------------------------------------
333:(362,2.16,2018-02-22)
444:(362,13.10,2015-01-09),(430,13.05,2017-04-01),(550,13.30,2018-02-01)

How to quickly combine text and date into same cell in Excel?, Supposing you have two columns which one contains text strings and another is filled with date, now, you Combine text and date into one cell with Kutools for Excel you can merge multiple columns, rows and cells into one cell without losing 1. Select the data range that you want to combine. 2. Click Kutools > Merge  if one of the column is number i have experienced the oracle will think '+' as sum operator instead concatenation. eg: select (id + name) as one from table 1; (id is numeric) throws invalid number exception. in such case you can || operator which is concatenation. select (id || name) as one from table 1;


The following query works with the sample data provided:

;WITH VendorPerItemCTE AS (
   SELECT ItemCode,
          VendorCode,
          UnitCost,
          StartingDate,
          ROW_NUMBER() OVER (PARTITION BY ItemCode, VendorCode 
                             ORDER BY StartingDate DESC) AS seq
  FROM PriceList
)      
SELECT CAST(ItemCode AS VARCHAR(12)) + ':' + STUFF(ItemData , 1, 1, '')
FROM (
   SELECT DISTINCT p.ItemCode,
          (SELECT ', (' + CAST(VendorCode AS VARCHAR(10)) + ', ' + 
                        CAST(UnitCost AS VARCHAR(10)) + ', ' + 
                        CONVERT(VARCHAR(12), StartingDate, 102 ) +
                  ')'
          FROM VendorPerItemCTE AS c
          WHERE p.ItemCode = c.ItemCode AND c.seq = 1      
          FOR XML PATH('')) AS ItemData
   FROM PriceList AS p) AS t

Demo here

How to Concatenate Multiple columns in MySQL, In MysQL you concatenate multiple columns, by using CONCAT and CONCAT_WS function. You can simply do this programmatically by separately select fields from Table you want to show both the columns values in a single string form. Previous Post MySQL – Convert Unix Timestamp to Date Time. There is new method in SQL Server 2017: SELECT STRING_AGG (column, ',') AS column FROM Table; This a stab at creating a reusable column to comma separated string. In this case, I only one strings that have values and I do not want empty strings or nulls. First I create a user defined type that is a one column table.


CONCATENATE in Excel: combine text strings, cells and columns, CONCATENATE in Excel: combine text strings, cells and columns As a result, you have one larger cell that is displayed across multiple rows and/or columns in your worksheet. Press CTRL to select multiple cells to be concatenated I will remind you that when combining a text string and date, you  If we take this a step further, we can use the FOR XML PATH option to return the results as an XML string which will put all of the data into one row and one column. SELECT SS.SEC_NAME, US.USR_NAME FROM SALES_SECTORS SS INNER JOIN USRS US ON US.SEC_ID = SS.SEC_ID ORDER BY 1, 2 FOR XML PATH('') SQL Server Example to Rolling up Multiple Rows into


Get multiple columns from a select subquery, date` as special_date FROM product_special ps INNER JOIN ( SELECT id, MIN(​priority) as min_priority Admittedly it's still just one column, but in my code, I can easily access the two values. And later draw your data using sub-strings. In Excel, you can combine or merge text from two or more cells, as well as columns and rows,into one cell. If you have a lot of rows of data where you want to combine text, you can simply start typing the combined text in an adjacent column and Excel will fill in the rest for you.


How to convert multiple columns to string in pandas dataframe , 1 answer to this question. To convert multiple columns to string, include a list of columns to your above-mentioned How to convert date string to date object in python? Use this how can i randomly select items from a list? > Combining Data from Separate Columns into a Single Date and Time Value Combining Data from Separate Columns into a Single Date and Time Value While working in Microsoft Excel, it is very common that we have to work on combining data from multiple columns in a worksheet in Microsoft Excel 2010.