October 12, 2016 at 7:15 am
I have got following data in my table
machine_idtype_idattribute_nameattribute_value
11issuer0cn=test
11issuer1cn=test1
11NotAfter01/1/2016
11NotAfter11/1/2016
11NotBefore01/1/2016
11NotBefore11/1/2016
11subject0cn=test
11subject1cn=test1
I would like to have it formatted/returned like below
machine_idtype_idissuernotbeforenotaftersubject
11cn=test1/1/20161/1/2016cn=test
11cn=test1 1/1/20161/1/2016cn=test1
I've tried using Pivot but then I'm only able to do this if I specify the individual columns, and it would return everything in 1 row for each machine.
The challenge here is that some machines can return 10-15 individual certificates so i would like to have these shown as individual rows for each certificate.
Below is sample code to create table/data.
thx again for all help.
CREATE TABLE dbo.testreg (
machine_id [int] NOT NULL,
type_id [int] NOT NULL,
attribute_name [varchar](max) NULL,
attribute_value [varchar](max) NULL
) ON [invdatagroup]
INSERT INTO dbo.testreg (machine_id, type_id, attribute_name, attribute_value) VALUES (1,1,'issuer0','cn=test')
INSERT INTO dbo.testreg (machine_id, type_id, attribute_name, attribute_value) VALUES (1,1,'issuer1','cn=test1')
INSERT INTO dbo.testreg (machine_id, type_id, attribute_name, attribute_value) VALUES (1,1,'NotAfter0','1/1/2016')
INSERT INTO dbo.testreg (machine_id, type_id, attribute_name, attribute_value) VALUES (1,1,'NotAfter1','1/1/2016')
INSERT INTO dbo.testreg (machine_id, type_id, attribute_name, attribute_value) VALUES (1,1,'NotBefore0','1/1/2016')
INSERT INTO dbo.testreg (machine_id, type_id, attribute_name, attribute_value) VALUES (1,1,'NotBefore1','1/1/2016')
INSERT INTO dbo.testreg (machine_id, type_id, attribute_name, attribute_value) VALUES (1,1,'subject0','cn=test')
INSERT INTO dbo.testreg (machine_id, type_id, attribute_name, attribute_value) VALUES (1,1,'subject1','cn=test1')
select * from dbo.testreg
October 12, 2016 at 7:22 am
October 12, 2016 at 7:29 am
How do you know which rows are part of the same "row"? Please tell me it isn't the last part of the attribute_name value. EAV is a great tool when done correctly and in the right situations. I can't speak to the situation here but the data makes me cringe as you are trying to put this back together because you don't seem to have a column that you can use to indicate a given instance of the data.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 12, 2016 at 7:32 am
And here's an example based on that article (you'll need to complete it):
SELECT machine_id,
type_id,
MAX( CASE WHEN attribute_name LIKE 'issuer%' THEN attribute_value END) AS issuer
FROM dbo.testreg
GROUP BY machine_id,
type_id,
RIGHT(RTRIM(attribute_name), 1);
October 12, 2016 at 7:47 am
Luis Cazares (10/12/2016)
And here's an example based on that article (you'll need to complete it):
SELECT machine_id,
type_id,
MAX( CASE WHEN attribute_name LIKE 'issuer%' THEN attribute_value END) AS issuer
FROM dbo.testreg
GROUP BY machine_id,
type_id,
RIGHT(RTRIM(attribute_name), 1);
And this great example demonstrates how brittle the original data structure really is. The OP stated they may have 10-15 rows of data. This hints that they can't use the last character of attribute_name but the last x characters which are numeric. So the challenge of querying a poor structure has gotten more complicated by stuffing multiple values into a single tuple.
I am by no means trying to be critical of Luis' excellent code here. It is the underlying table that causes so much problem.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 12, 2016 at 8:35 am
the solution proposed by John/Louis does the trick and I'm able to query data for each end point. Thx again for all the help.
October 13, 2016 at 12:48 pm
Bluntly, but we 00 thing you have done is right proper>> I have got following data in my table <<
if you are doing the wrong thing badly.
Where is the DDL for this mess? Why are you mixing data and metadata in the same table? Why do not you know the correct ISO 8601 date format? Why do not you know there is no such thing as a "type_id"?
These are not tricky, complicated RDBMS things. This is foundations and basics! Just look at what a piece of crap your "attribute_value" is! In total violation of first normal form. Sometimes it is an assignment statement, sometimes it is a date (in improper format). Not only are you doing the wrong thing, what you are doing it badly.
I always love it when someone says things like "type_id"; it makes me feel like my 30+ years with RDBMS were wasted. An attribute can be a "<something in particular>_type" or a"<something in particular>_id", but never this weird hybrid. "_type" and "_id" are called attribute properties in data modeling. They have to be attached to an attribute (column name).
>> The challenge here is that some machines can return 10-15 individual certificates so I would like to have these shown as individual rows for each certificate. <<
Are the certificates actually individual things? Apparently not in your model! What you tried to post is DDL does not include a key (what is the definition of a table? Remember that from your first day of RDBMS class?) I am identifier cannot be an integer, by definition; you do not do any math on it. I have already told you why type ID is absurd. Your use of VARCHAR(max), without any constraints is going to simply fill this non-schema with garbage.
Please check my credentials. I am telling you that everything you are doing is dangerously wrong. You should not be programming in SQL yet. 85 – 95% of the work is done in the DDL. Once a valid schema is set up the queries practically write themselves. Please start over and do it right.
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply