Member Avatar for ma.ali786

hi,
how to create nested table in sql server for example
employee table contains 3 column
id
name
address

where name should contain 3 sub columns like
firstname
middlename
lastname

how can i get this one help me plz

  • 5 Contributors
  • 5 Replies
  • 260 Views
  • 6 Months Discussion Span
  • Latest Post Latest Post by adam_k

Dani AI

Generated

Short answer: SQL Server’s relational engine does not provide Oracle-style "nested table" columns for OLTP. was essentially correct. pointed to a Microsoft page about nested tables, but that feature lives in SQL Server Analysis Services (data mining) — not as a column type you can use in regular tables; ’s warning about the SSAS/OLTP difference is right. (learn.microsoft.com)

A practical, robust approach for employees and name parts is normalization: put name rows in a child table (one row per name/alias or per name-type) instead of embedding a collection inside a column. That makes querying, indexing and constraints simple. Example schema (illustrative):

CREATE TABLE Employee (
  EmployeeID INT PRIMARY KEY,
  Address NVARCHAR(250)
);

CREATE TABLE EmployeeName (
  EmployeeNameID INT IDENTITY PRIMARY KEY,
  EmployeeID INT NOT NULL REFERENCES Employee(EmployeeID),
  NameType NVARCHAR(20) NOT NULL,  -- 'legal','preferred','alias'
  GivenName NVARCHAR(100),
  MiddleName NVARCHAR(100),
  FamilyName NVARCHAR(100)
);

Normalization avoids repeating-group problems and is the standard OLTP pattern. (digitalocean.com)

If you need flexible, semi-structured storage (variable parts per row), SQL Server supports XML (rich XQuery/XML methods) and native JSON functions (OPENJSON, JSON_VALUE, etc.). Those let you store a blob and still shred it into rows/columns when needed. Example (JSON):

INSERT INTO Employee (EmployeeID, NameJson, Address)
VALUES (1, N'{"first":"John","middle":"K","last":"Doe"}', '...');

SELECT e.EmployeeID, j.first, j.middle, j.last
FROM Employee e
CROSS APPLY OPENJSON(e.NameJson)
  WITH (first nvarchar(100) '$.first',
        middle nvarchar(100) '$.middle',
        last nvarchar(100) '$.last') AS j;

Use XML .nodes()/.value() when you prefer XML. Be aware of trade-offs (indexing, size, query patterns). (learn.microsoft.com)

If the original goal was simply to pass a list/rows from an application into a stored proc, consider table-valued parameters (UDTT/TVP) — they are for parameters, not persistent nested columns, and have their own limitations (read-only in the proc, etc.). Choose the pattern (normalized table vs JSON/XML vs TVP) based on how you must query, index, and validate name parts. (learn.microsoft.com)

Recommended Answers

All 5 Replies

Member Avatar for adam_k

The short answer is you can't. Oracle has nested tables, but MS SQL server doesn't.
If you describe why you want this / what are you trying to achieve perhaps we can find you with an alternative (like 6 columns in your employee table that you concatenate in a view or a second table that will hold your name data).

Edited by adam_k because: n/a
Member Avatar for BitBlt

That's only in Analysis Services. If you want a general relational table structure (as in you can use it for OLTP), SQL Server doesn't support nested tables.

Member Avatar for Eagletalon

Create a table with "id", "firstname", "middlename", "lastname", "address"

And select them into 1 field... closest I think you are going to get to what you seek...

Alternatively you can try different select statements or tricks to display in different ways... but I dont believe the nested tables are gonne fly

Member Avatar for adam_k

Depending on the need/application you could use a varchar field that would separate "fields" with a specific char and then either use charindex or split into an array from the front end or store XML into the field or ... , but not of these are a nested table.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.