CODEWITHSUNDEEP
sql-server
FDavidov
FDavidov

Reputation: 3667

SQL Server - Is it possible to define a table column as a table?

I know that this is possible in Oracle and I wonder if SQL Server also supports it (searched for answer without success).

It would greatly simplify my life in the current project if I could define a column of a table to be a table itself, something like:

Table A:
  Column_1    Column_2
+----------+----------------------------------------+
|     1    |   Columns_2_1       Column_2_2         |
|          |  +-------------+------------------+    |
|          |  |  'A'        |    12345         |    |
|          |  +-------------+------------------+    |
|          |  |  'B'        |    777777        |    |
|          |  +-------------+------------------+    |
|          |  |  'C'        |    888888        |    |
|          |  +-------------+------------------+    |
+----------+----------------------------------------+
|     2    |   Columns_2_1       Column_2_2         |
|          |  +-------------+------------------+    |
|          |  |  'X'        |    555555        |    |
|          |  +-------------+------------------+    |
|          |  |  'Y'        |    666666        |    |
|          |  +-------------+------------------+    |
|          |  |  'Z'        |    000001        |    |
|          |  +-------------+------------------+    |
+----------+----------------------------------------+

Thanks in advance.

Upvotes: 0

Views: 52

Answers (2)

Aasish Kr. Sharma
Aasish Kr. Sharma

Reputation: 556

Actually, for a normalized database we do not require such functionality. Because if we need to insert a table within a column than we can create a child table and reference it as a foreign key in the parent table.

In spite, if you still insist to such functionality than you can use SQL Server 2016 to support JSON data where you can store any associative list in JSON format. Like:

DECLARE @json NVARCHAR(4000)
SET @json = 
N'{
    "info":{  
        "type":1,

        "address":{  
        "town":"Bristol",
        "county":"Avon",
        "country":"England"
        },
        "tags":["Sport", "Water polo"]
    },
    "type":"Basic"
}'

SELECT
    JSON_VALUE(@json, '$.type') as type,
    JSON_VALUE(@json, '$.info.address.town') as town,
    JSON_QUERY(@json, '$.info.tags') as tags

SELECT value
FROM OPENJSON(@json, '$.info.tags')

In older versions, this can be achieved through xml as shown in previous answer.

Your can also make use of "sql_variant" datatype to map your table.

Previously, I was also in search of such features as available in Oracle. But after reading various articles and blogs from experts, I was convinced, such features will make the things more complex beside helping. Only storing the data in required format is not important, It is worthy when it is also efficiently available (readable).

Hope this will help you to take your decision.

Upvotes: 0

John Cappelletti
John Cappelletti

Reputation: 82010

There is one option where you can store data as XML

Declare @YourTable table (ID int,XMLData xml)
Insert Into @YourTable values 
 (1,'<root><ID>1</ID><Active>1</Active><First_Name>John</First_Name><Last_Name>Smith</Last_Name><EMail>[email protected]</EMail></root>')
,(2,'<root><ID>2</ID><Active>0</Active><First_Name>Jane</First_Name><Last_Name>Doe</Last_Name><EMail>[email protected]</EMail></root>')

Select ID 
      ,Last_Name  = XMLData.value('(root/Last_Name)[1]'  ,'nvarchar(50)')
      ,First_Name = XMLData.value('(root/First_Name)[1]' ,'nvarchar(50)')
 From @YourTable

Returns

ID  Last_Name   First_Name
1   Smith       John
2   Doe         Jane

Upvotes: 2

Related Questions