Click here to Skip to main content
14,689,406 members
Home » Discussions » Database
   

Database

 
GeneralRe: Need help mixing two queries in one... Pin
Jörgen Andersson2-Sep-19 20:29
professionalJörgen Andersson2-Sep-19 20:29 
QuestionAdvice or opinion on database planning for Angular using MongoDB and perhaps a 2nd technology. Pin
jkirkerx30-Aug-19 9:16
professionaljkirkerx30-Aug-19 9:16 
AnswerRe: Advice or opinion on database planning for Angular using MongoDB and perhaps a 2nd technology. Pin
Afzaal Ahmad Zeeshan30-Aug-19 12:56
mvaAfzaal Ahmad Zeeshan30-Aug-19 12:56 
GeneralRe: Advice or opinion on database planning for Angular using MongoDB and perhaps a 2nd technology. Pin
jkirkerx1-Sep-19 11:46
professionaljkirkerx1-Sep-19 11:46 
QuestionCompact and Repair Access Database using C# ? Pin
Member 245846729-Aug-19 16:14
MemberMember 245846729-Aug-19 16:14 
AnswerRe: Compact and Repair Access Database using C# ? Pin
Victor Nijegorodov29-Aug-19 21:37
MemberVictor Nijegorodov29-Aug-19 21:37 
QuestionHwere's a Head-Scratcher Pin
#realJSOP28-Aug-19 10:37
mva#realJSOP28-Aug-19 10:37 
AnswerRe: Hwere's a Head-Scratcher Pin
Richard Deeming28-Aug-19 11:00
mveRichard Deeming28-Aug-19 11: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 13:42
professionalMycroft Holmes28-Aug-19 13:42 
GeneralRe: Hwere's a Head-Scratcher Pin
#realJSOP29-Aug-19 0:23
mva#realJSOP29-Aug-19 0:23 
GeneralRe: Hwere's a Head-Scratcher Pin
Richard Deeming29-Aug-19 2:22
mveRichard Deeming29-Aug-19 2:22 
GeneralRe: Hwere's a Head-Scratcher Pin
#realJSOP29-Aug-19 2:47
mva#realJSOP29-Aug-19 2:47 
GeneralRe: Hwere's a Head-Scratcher Pin
Richard Deeming29-Aug-19 2:55
mveRichard Deeming29-Aug-19 2:55 
GeneralRe: Hwere's a Head-Scratcher Pin
#realJSOP29-Aug-19 2:59
mva#realJSOP29-Aug-19 2:59 
QuestionError With Simple Script Pin
Kevin Marois22-Aug-19 9:26
professionalKevin Marois22-Aug-19 9:26 
AnswerRe: Error With Simple Script Pin
Richard Deeming23-Aug-19 2:08
mveRichard Deeming23-Aug-19 2:08 
GeneralRe: Error With Simple Script Pin
Kevin Marois23-Aug-19 7:21
professionalKevin Marois23-Aug-19 7:21 
QuestionError With Simple Script Pin
Kevin Marois22-Aug-19 9:25
professionalKevin Marois22-Aug-19 9:25 
AnswerRe: Error With Simple Script Pin
David Mujica24-Oct-19 10:56
MemberDavid Mujica24-Oct-19 10:56 
QuestionAccess SQL Server Remotely Via VPN Pin
Kevin Marois20-Aug-19 6:40
professionalKevin Marois20-Aug-19 6:40 
AnswerRe: Access SQL Server Remotely Via VPN Pin
Richard Deeming20-Aug-19 6:56
mveRichard Deeming20-Aug-19 6:56 
QuestionSQL Remote Connection Problem Pin
Kevin Marois19-Aug-19 8:52
professionalKevin Marois19-Aug-19 8:52 
AnswerRe: SQL Remote Connection Problem Pin
Richard Deeming19-Aug-19 9:45
mveRichard Deeming19-Aug-19 9:45 
GeneralRe: SQL Remote Connection Problem Pin
Kevin Marois19-Aug-19 10:21
professionalKevin Marois19-Aug-19 10:21 
GeneralRe: SQL Remote Connection Problem Pin
Richard Deeming19-Aug-19 10:28
mveRichard Deeming19-Aug-19 10:28 

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.