Click here to Skip to main content
14,331,194 members
   

Database

 
AnswerMessage Closed Pin
16-Sep-19 20:25
membergentorify16-Sep-19 20:25 
QuestionAdvice or opinion on database planning for Angular using MongoDB and perhaps a 2nd technology. Pin
jkirkerx30-Aug-19 8:16
professionaljkirkerx30-Aug-19 8:16 
AnswerRe: Advice or opinion on database planning for Angular using MongoDB and perhaps a 2nd technology. Pin
Afzaal Ahmad Zeeshan30-Aug-19 11:56
mveAfzaal Ahmad Zeeshan30-Aug-19 11:56 
GeneralRe: Advice or opinion on database planning for Angular using MongoDB and perhaps a 2nd technology. Pin
jkirkerx1-Sep-19 10:46
professionaljkirkerx1-Sep-19 10:46 
QuestionCompact and Repair Access Database using C# ? Pin
Member 245846729-Aug-19 15:14
memberMember 245846729-Aug-19 15:14 
AnswerRe: Compact and Repair Access Database using C# ? Pin
Victor Nijegorodov29-Aug-19 20:37
memberVictor Nijegorodov29-Aug-19 20:37 
QuestionHwere's a Head-Scratcher Pin
#realJSOP28-Aug-19 9:37
mve#realJSOP28-Aug-19 9:37 
AnswerRe: Hwere's a Head-Scratcher Pin
Richard Deeming28-Aug-19 10:00
mveRichard Deeming28-Aug-19 10:00 
Depends what you mean by "better". Smile | :)

You're using SQL 2016, so STRING_AGG[^] is out - that was added in 2017.

But you can use STRING_SPLIT[^], TRY_PARSE[^], and FORMAT[^]. And there are ways[^] to concatenate row values in 2016 and earlier.

I've assumed a table without a primary key; if your table has one, use that instead of the generated ROW_NUMBER:
WITH cteRN As
(
    SELECT
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) As RN,
        tree
    FROM
        MyTable
)
SELECT
    STUFF(
        (
            SELECT '.' + IsNull(Format(Try_Parse(P.value As int), 'D2'), P.value)
            FROM cteRN As T2 
            CROSS APPLY string_split(T2.tree, '.') As P
            WHERE T2.RN = T.RN
            FOR XML PATH(''), TYPE
        ).value('.', 'varchar(max)')
    , 1, 1, '') As tree
FROM
    cteRN As T
GROUP BY
    T.RN
;
It's not pretty, but it works:
01.00
01.00.01
01.00.02
01.00.02.01
01.00.02.02
01.10.01.35




"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

GeneralRe: Hwere's a Head-Scratcher Pin
Mycroft Holmes28-Aug-19 12:42
memberMycroft Holmes28-Aug-19 12:42 
GeneralRe: Hwere's a Head-Scratcher Pin
#realJSOP28-Aug-19 23:23
mve#realJSOP28-Aug-19 23:23 
GeneralRe: Hwere's a Head-Scratcher Pin
Richard Deeming29-Aug-19 1:22
mveRichard Deeming29-Aug-19 1:22 
GeneralRe: Hwere's a Head-Scratcher Pin
#realJSOP29-Aug-19 1:47
mve#realJSOP29-Aug-19 1:47 
GeneralRe: Hwere's a Head-Scratcher Pin
Richard Deeming29-Aug-19 1:55
mveRichard Deeming29-Aug-19 1:55 
GeneralRe: Hwere's a Head-Scratcher Pin
#realJSOP29-Aug-19 1:59
mve#realJSOP29-Aug-19 1:59 
QuestionError With Simple Script Pin
Kevin Marois22-Aug-19 8:26
professionalKevin Marois22-Aug-19 8:26 
AnswerRe: Error With Simple Script Pin
Richard Deeming23-Aug-19 1:08
mveRichard Deeming23-Aug-19 1:08 
GeneralRe: Error With Simple Script Pin
Kevin Marois23-Aug-19 6:21
professionalKevin Marois23-Aug-19 6:21 
QuestionAccess SQL Server Remotely Via VPN Pin
Kevin Marois20-Aug-19 5:40
professionalKevin Marois20-Aug-19 5:40 
AnswerRe: Access SQL Server Remotely Via VPN Pin
Richard Deeming20-Aug-19 5:56
mveRichard Deeming20-Aug-19 5:56 
QuestionSQL Remote Connection Problem Pin
Kevin Marois19-Aug-19 7:52
professionalKevin Marois19-Aug-19 7:52 
AnswerRe: SQL Remote Connection Problem Pin
Richard Deeming19-Aug-19 8:45
mveRichard Deeming19-Aug-19 8:45 
GeneralRe: SQL Remote Connection Problem Pin
Kevin Marois19-Aug-19 9:21
professionalKevin Marois19-Aug-19 9:21 
GeneralRe: SQL Remote Connection Problem Pin
Richard Deeming19-Aug-19 9:28
mveRichard Deeming19-Aug-19 9:28 
GeneralRe: SQL Remote Connection Problem Pin
Kevin Marois19-Aug-19 9:42
professionalKevin Marois19-Aug-19 9:42 
QuestionDate format in SQL Database export to Excel 23/01/1990 00:00:00 instat of 23/01/1990 Pin
jan Meeling18-Aug-19 22:15
memberjan Meeling18-Aug-19 22:15 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.