Selecting multiple columns to one string (by date)
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:
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);
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
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.