Click here to Skip to main content
14,737,711 members
Home » Discussions » Database
   

Database

 
QuestionProduct Architecture Pin
Chiranjana13-Aug-19 1:50
MemberChiranjana13-Aug-19 1:50 
AnswerRe: Product Architecture Pin
David Mujica13-Aug-19 4:50
MemberDavid Mujica13-Aug-19 4:50 
GeneralRe: Product Architecture Pin
Chiranjana13-Aug-19 6:05
MemberChiranjana13-Aug-19 6:05 
AnswerRe: Product Architecture Pin
Mycroft Holmes13-Aug-19 13:50
professionalMycroft Holmes13-Aug-19 13:50 
Questioncookies vs sessions for storing users id for ecommerce site Pin
djtrixy2-Aug-19 22:48
Memberdjtrixy2-Aug-19 22:48 
AnswerRe: cookies vs sessions for storing users id for ecommerce site Pin
Afzaal Ahmad Zeeshan3-Aug-19 5:02
mveAfzaal Ahmad Zeeshan3-Aug-19 5:02 
QuestionI didn't design it but I need to get data out of it. Pin
rnbergren2-Aug-19 5:23
Memberrnbergren2-Aug-19 5:23 
AnswerRe: I didn't design it but I need to get data out of it. Pin
Richard Deeming2-Aug-19 5:51
mveRichard Deeming2-Aug-19 5: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

GeneralRe: I didn't design it but I need to get data out of it. Pin
rnbergren2-Aug-19 9:12
Memberrnbergren2-Aug-19 9:12 
AnswerRe: I didn't design it but I need to get data out of it. Pin
ZurdoDev2-Aug-19 11:33
professionalZurdoDev2-Aug-19 11:33 
AnswerRe: I didn't design it but I need to get data out of it. Pin
Mycroft Holmes2-Aug-19 14:40
professionalMycroft Holmes2-Aug-19 14:40 
QuestionSQL Server: Facing problem to parse xml using xquery Pin
Mou_kol31-Jul-19 6:56
MemberMou_kol31-Jul-19 6:56 
Questionstructure question 2 Pin
Joan M18-Jul-19 22:25
professionalJoan M18-Jul-19 22:25 
AnswerRe: structure question 2 Pin
Richard Deeming19-Jul-19 2:11
mveRichard Deeming19-Jul-19 2:11 
GeneralRe: structure question 2 Pin
Joan M19-Jul-19 2:14
professionalJoan M19-Jul-19 2:14 
GeneralRe: structure question 2 Pin
Richard Deeming19-Jul-19 2:27
mveRichard Deeming19-Jul-19 2:27 
PraiseRe: structure question 2 Pin
Joan M19-Jul-19 2:31
professionalJoan M19-Jul-19 2:31 
QuestionBest practices for database tables. Pin
RLD7114-Jul-19 13:37
professionalRLD7114-Jul-19 13:37 
AnswerRe: Best practices for database tables. Pin
CHill6018-Jul-19 0:17
mveCHill6018-Jul-19 0:17 
AnswerRe: Best practices for database tables. Pin
Mycroft Holmes18-Jul-19 12:57
professionalMycroft Holmes18-Jul-19 12:57 
GeneralRe: Best practices for database tables. Pin
RLD7121-Jul-19 4:37
professionalRLD7121-Jul-19 4:37 
Questionstructure question Pin
Joan M11-Jul-19 2:30
professionalJoan M11-Jul-19 2:30 
AnswerRe: structure question Pin
Eddy Vluggen11-Jul-19 3:01
professionalEddy Vluggen11-Jul-19 3:01 
GeneralRe: structure question Pin
CHill6018-Jul-19 2:30
mveCHill6018-Jul-19 2:30 
GeneralRe: structure question Pin
Eddy Vluggen18-Jul-19 23:53
professionalEddy Vluggen18-Jul-19 23:53 

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.