Click here to Skip to main content
15,907,281 members
Home / Discussions / Database
   

Database

 
QuestionProduct Architecture Pin
Chiranjana13-Aug-19 0:50
Chiranjana13-Aug-19 0:50 
AnswerRe: Product Architecture Pin
David Mujica13-Aug-19 3:50
David Mujica13-Aug-19 3:50 
GeneralRe: Product Architecture Pin
Chiranjana13-Aug-19 5:05
Chiranjana13-Aug-19 5:05 
AnswerRe: Product Architecture Pin
Mycroft Holmes13-Aug-19 12:50
professionalMycroft Holmes13-Aug-19 12:50 
Questioncookies vs sessions for storing users id for ecommerce site Pin
djtrixy2-Aug-19 21:48
djtrixy2-Aug-19 21:48 
AnswerRe: cookies vs sessions for storing users id for ecommerce site Pin
Afzaal Ahmad Zeeshan3-Aug-19 4:02
professionalAfzaal 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
rnbergren2-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:
SQL
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:
SQL
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 8:12
rnbergren2-Aug-19 8:12 
AnswerRe: I didn't design it but I need to get data out of it. Pin
ZurdoDev2-Aug-19 10:33
professionalZurdoDev2-Aug-19 10:33 
AnswerRe: I didn't design it but I need to get data out of it. Pin
Mycroft Holmes2-Aug-19 13:40
professionalMycroft Holmes2-Aug-19 13:40 
QuestionSQL Server: Facing problem to parse xml using xquery Pin
Mou_kol31-Jul-19 5:56
Mou_kol31-Jul-19 5:56 
Questionstructure question 2 Pin
Joan M18-Jul-19 21:25
professionalJoan M18-Jul-19 21:25 
AnswerRe: structure question 2 Pin
Richard Deeming19-Jul-19 1:11
mveRichard Deeming19-Jul-19 1:11 
GeneralRe: structure question 2 Pin
Joan M19-Jul-19 1:14
professionalJoan M19-Jul-19 1:14 
GeneralRe: structure question 2 Pin
Richard Deeming19-Jul-19 1:27
mveRichard Deeming19-Jul-19 1:27 
PraiseRe: structure question 2 Pin
Joan M19-Jul-19 1:31
professionalJoan M19-Jul-19 1:31 
QuestionBest practices for database tables. Pin
RLD7114-Jul-19 12:37
professionalRLD7114-Jul-19 12:37 
AnswerRe: Best practices for database tables. Pin
CHill6017-Jul-19 23:17
mveCHill6017-Jul-19 23:17 
AnswerRe: Best practices for database tables. Pin
Mycroft Holmes18-Jul-19 11:57
professionalMycroft Holmes18-Jul-19 11:57 
GeneralRe: Best practices for database tables. Pin
RLD7121-Jul-19 3:37
professionalRLD7121-Jul-19 3:37 
Questionstructure question Pin
Joan M11-Jul-19 1:30
professionalJoan M11-Jul-19 1:30 
AnswerRe: structure question Pin
Eddy Vluggen11-Jul-19 2:01
professionalEddy Vluggen11-Jul-19 2:01 
GeneralRe: structure question Pin
CHill6018-Jul-19 1:30
mveCHill6018-Jul-19 1:30 
GeneralRe: structure question Pin
Eddy Vluggen18-Jul-19 22:53
professionalEddy Vluggen18-Jul-19 22: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.