Click here to Skip to main content
14,267,388 members
   

Database

 
PinnedHOW TO ANSWER A QUESTION PinPopular
Chris Maunder16-Jul-09 3:10
adminChris Maunder16-Jul-09 3:10 
PinnedHow to get an answer to your question PinPopular
Chris Maunder10-Nov-05 16:30
adminChris Maunder10-Nov-05 16:30 
QuestionSQL Remote Connection Problem Pin
Kevin Marois12hrs 10mins ago
professionalKevin Marois12hrs 10mins ago 
AnswerRe: SQL Remote Connection Problem Pin
Richard Deeming11hrs 17mins ago
mveRichard Deeming11hrs 17mins ago 
GeneralRe: SQL Remote Connection Problem Pin
Kevin Marois10hrs 41mins ago
professionalKevin Marois10hrs 41mins ago 
GeneralRe: SQL Remote Connection Problem Pin
Richard Deeming10hrs 34mins ago
mveRichard Deeming10hrs 34mins ago 
GeneralRe: SQL Remote Connection Problem Pin
Kevin Marois10hrs 20mins ago
professionalKevin Marois10hrs 20mins ago 
QuestionDate format in SQL Database export to Excel 23/01/1990 00:00:00 instat of 23/01/1990 Pin
jan Meeling21hrs 47mins ago
memberjan Meeling21hrs 47mins ago 
QuestionRe: Date format in SQL Database export to Excel 23/01/1990 00:00:00 instat of 23/01/1990 Pin
Richard MacCutchan21hrs 8mins ago
protectorRichard MacCutchan21hrs 8mins ago 
AnswerRe: Date format in SQL Database export to Excel 23/01/1990 00:00:00 instat of 23/01/1990 Pin
jan Meeling18hrs 23mins ago
memberjan Meeling18hrs 23mins ago 
GeneralRe: Date format in SQL Database export to Excel 23/01/1990 00:00:00 instat of 23/01/1990 Pin
Victor Nijegorodov16hrs 42mins ago
memberVictor Nijegorodov16hrs 42mins ago 
GeneralRe: Date format in SQL Database export to Excel 23/01/1990 00:00:00 instat of 23/01/1990 Pin
Richard MacCutchan15hrs 39mins ago
protectorRichard MacCutchan15hrs 39mins ago 
AnswerRe: Date format in SQL Database export to Excel 23/01/1990 00:00:00 instat of 23/01/1990 Pin
Victor Nijegorodov19hrs 43mins ago
memberVictor Nijegorodov19hrs 43mins ago 
GeneralRe: Date format in SQL Database export to Excel 23/01/1990 00:00:00 instat of 23/01/1990 Pin
jan Meeling18hrs 19mins ago
memberjan Meeling18hrs 19mins ago 
AnswerRe: Date format in SQL Database export to Excel 23/01/1990 00:00:00 instat of 23/01/1990 Pin
Eddy Vluggen18hrs 50mins ago
mveEddy Vluggen18hrs 50mins ago 
GeneralRe: Date format in SQL Database export to Excel 23/01/1990 00:00:00 instat of 23/01/1990 Pin
jan Meeling18hrs 7mins ago
memberjan Meeling18hrs 7mins ago 
GeneralRe: Date format in SQL Database export to Excel 23/01/1990 00:00:00 instat of 23/01/1990 Pin
Eddy Vluggen17hrs 59mins ago
mveEddy Vluggen17hrs 59mins ago 
QuestionProduct Architecture Pin
Chiranjana13-Aug-19 0:50
memberChiranjana13-Aug-19 0:50 
AnswerRe: Product Architecture Pin
David Mujica13-Aug-19 3:50
memberDavid Mujica13-Aug-19 3:50 
GeneralRe: Product Architecture Pin
Chiranjana13-Aug-19 5:05
memberChiranjana13-Aug-19 5:05 
AnswerRe: Product Architecture Pin
Mycroft Holmes13-Aug-19 12:50
memberMycroft Holmes13-Aug-19 12:50 
Questioncookies vs sessions for storing users id for ecommerce site Pin
djtrixy2-Aug-19 21:48
memberdjtrixy2-Aug-19 21:48 
AnswerRe: cookies vs sessions for storing users id for ecommerce site Pin
Afzaal Ahmad Zeeshan3-Aug-19 4:02
mveAfzaal Ahmad Zeeshan3-Aug-19 4:02 
QuestionI didn't design it but I need to get data out of it. Pin
rnbergren2-Aug-19 4:23
memberrnbergren2-Aug-19 4:23 
AnswerRe: I didn't design it but I need to get data out of it. Pin
Richard Deeming2-Aug-19 4:51
mveRichard Deeming2-Aug-19 4:51 
Not particularly simple, since different rows could have different "attributes" within the categories column.

Something like this should work:
DROP TABLE IF EXISTS #T;

CREATE TABLE #T
(
    ID int, 
    PropertyName varchar(50),
    PropertyValue varchar(50)
);

INSERT INTO #T
(
    ID,
    PropertyName,
    PropertyValue
)
SELECT
    T.ID,
    LTRIM(LEFT(V.value, CHARINDEX(':', V.value) - 1)) As PropertyName,
    LTRIM(SUBSTRING(V.value, CHARINDEX(':', V.value) + 1, LEN(V.Value))) As PropertyValue
FROM
    YourTable As T
    CROSS APPLY string_split(T.Categories, ',') As V
;

DECLARE @columns nvarchar(max) = STUFF
(
    (
        SELECT DISTINCT ',' + QUOTENAME(PropertyName) 
        FROM #T 
        FOR XML PATH(''), TYPE
    ).value('.', 'nvarchar(max)')
    , 1, 1, ''
);

DECLARE @query nvarchar(max) = N'SELECT ID, ' + @columns 
    + N' FROM (SELECT ID, PropertyName, PropertyValue FROM #T) As T'
    + N' PIVOT (Max(PropertyValue) FOR PropertyName In (' + @columns + N')) As P';

EXECUTE (@query);

DROP TABLE #T;
The @query will look something like:
SELECT ID, [Phone],[Street],[Town] 
FROM (SELECT ID, PropertyName, PropertyValue FROM #T) As T 
PIVOT (Max(PropertyValue) FOR PropertyName In ([Phone],[Street],[Town])) As P

STRING_SPLIT (Transact-SQL) - SQL Server | Microsoft Docs[^]
Using PIVOT and UNPIVOT - SQL Server | Microsoft Docs[^]



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

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.