asked
duplicate two column
Hi i want to find the query that helps me to find the OUTPUT Column in the table Below . I tried the whole day figuring it out and i was not successful . I used min , max and other . Please help ![alt text][1] [1]: /storage/temp/4461-capture.jpg
What you might want to consider using (and learning about) is the [row_number][1] from the family of [windowing, a.k.a. ranking functions][2]. Let's say for simplicity that you don't want to go too fancy but have the OUTPUT column as number (terrible name for the column though, because this is one of the reserved keywords, and so its use as the column name is best if avoided). In this case, the following script will work: select ID, [School Year], row_number() over (partition by ID order by [School Year]) [OUTPUT] from YourTableNameHere; This script will produce the following results: ID School Year OUTPUT --------- ----------- ----------------- C03000061 2014-2015 1 C03000062 2014-2015 1 C03000062 2015-2016 2 C03000062 2016-2017 3 C03000063 2015-2016 1 C03000063 2016-2017 2 If you really need the output column to be formatted exactly like you ask then you can use the same logic and apply the formatting as needed: select ID, [School Year], cast(row_number() over (partition by ID order by [School Year]) as varchar) + case row_number() over (partition by ID order by [School Year]) when 1 then 'st' when 2 then 'nd' when 3 then 'rd' else 'th' end + ' year' from YourTableNameHere; The results of this script, based on the sample data in question are: ID School Year OUTPUT --------- ----------- ------------------------ C03000061 2014-2015 1st year C03000062 2014-2015 1st year C03000062 2015-2016 2nd year C03000062 2016-2017 3rd year C03000063 2015-2016 1st year C03000063 2016-2017 2nd year Hope this helps. Oleg [1]:
https://docs.microsoft.com/en-us/sql/t-sql/functions/row-number-transact-sql [2]:
https://docs.microsoft.com/en-us/sql/t-sql/functions/ranking-functions-transact-sql
Thank you so much .. Its really helpful . thank you for the tip also
Dear ! I am so grateful for all the help ! we want to import data to salesforce cloud with as multi- picklist value and since the ID is duplicate , Salesforce is Overriding the value instead on appending it . when i google about it , my integration app needs to a semi-colon (;) to do that . Is there a way the data can look like the **red marked** column ? ![alt text][1] i tried to use join and concatenate it but i was not successful. Can you please help me? Thank you as always [1]: /storage/temp/4466-capture9.jpg
@fantumahi2 Yes, it is possible. I don't know which version of SQL Server you use (this information was never mentioned in question/comments). If the version is SQL Server 2016 then it is possible to use the [string_agg][1], if not then [FOR XML][2] technique may be used which will work for any version starting from SQL Server 2005. Here is the script which will produce desired output:
select distinct
ID, stuff(years.List, 1, 1, '') [OUTPUT]
from YourTableNameHere t cross apply (
select
';' + [School Year]
from YourTableNameHere where ID = t.ID
order by [School Year] for xml path('')
) years(List); Please read the referenced articles if you would like to learn about the details. [1]:
https://docs.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql [2]:
https://docs.microsoft.com/en-us/sql/relational-databases/xml/for-xml-sql-server
Thnak you so much .. i am done with my project . It shows i am behind the new cool futures Microsoft has. Thank you again.
18 People are following this question.
