Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
chaaru_akilan
Starting Member
16 Posts |
Posted - 2013-02-13 : 08:28:06
|
Hi,How to select distinct of only one column? and add unique numbering to the result set?I have a table as:REQID Name No1 ABC Null2 ABC Null3 CDE 784 GHF 80My O/p:ID Name No1 ABC Null2 CDE 783 GHF 80Thanks in advance |
|
Luuk123
Yak Posting Veteran
52 Posts |
Posted - 2013-02-13 : 08:35:04
|
You can use somethink like this:select t.name, row_number() over ( order by t.name )from ( select distinct name from table ) t |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-02-13 : 08:35:15
|
[code]SELECT ROW_NUMBER() OVER (ORDER BY reqid) AS ID, [name],noFROM( SELECT *, ROW_NUMBER() OVER (PARTITION BY [name] ORDER BY (SELECT 1)) AS RN FROM TheTable) s WHERE RN = 1;[/code] |
|
|
chaaru_akilan
Starting Member
16 Posts |
Posted - 2013-02-15 : 03:57:26
|
Hi, Thank you. I have another additional requirement! I need to concatenate the IDs in one column.Am unable to concatenate.I have a table as:REQID Name No1 ABC Null2 ABC Null3 CDE 784 GHF 80My O/p should beReqID Name No1,2 ABC Null3 CDE 784 GHF 80Thanks In advance |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-15 : 04:05:04
|
quote: Originally posted by chaaru_akilan Hi, Thank you. I have another additional requirement! I need to concatenate the IDs in one column.Am unable to concatenate.I have a table as:REQID Name No1 ABC Null2 ABC Null3 CDE 784 GHF 80My O/p should beReqID Name No1,2 ABC Null3 CDE 784 GHF 80Thanks In advance
SELECT STUFF((SELECT ',' + CAST(REQID AS varchar(10)) FROM TableWHERE Name= t.NameAND No = t.NoORDER BY REQIDFOR XML PATH('')),1,1,'') AS REQIDs,[Name],NULLIF(No,'Null') AS [No]FROM (SELECT DISTINCT [Name],COALESCE(No,'Null') AS [No]FROM table)t ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
chaaru_akilan
Starting Member
16 Posts |
Posted - 2013-02-15 : 04:10:27
|
Great, It worked as i needed!Thanks. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-15 : 04:13:38
|
welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|