Convert List to Table and Vice-Versa

In the real world, sometimes we face a scenario where we are required to convert a list into a table or vice versa. In this article first we will learn how to convert a list intoa table, and after that we will convert the table into a list.

Convert List into Table

Firstly, we create a user-defined function that converts the list to a table. We define two parameters in this function; the first parameter accepts the list and second parameter defines the delimiter and on the basis of this delimiter we separate the list.

Let us create a function

  1. CREATEFUNCTION UDF_ConvertListItem2Table
  2. (
  3. @ListItemVARCHAR(MAX),
  4. @Delimiter CHAR
  5. )
  6. RETURNS
  7. @Tab TABLE
  8. (
  9. Id intIDENTITY(1, 1),
  10. Item VARCHAR(MAX)
  11. )
  12. AS
  13. BEGIN
  14. DECLARE @Item VARCHAR(MAX);
  15. DECLARE @Index INT;
  16. SET @ListItem = LTRIM(RTRIM(@ListItem)) + @Delimiter
  17. SET @Index = CHARINDEX(@Delimiter, @ListItem)
  18. WHILE @Index > 0
  19. BEGIN
  20. SET @Item = LTRIM(RTRIM(LEFT(@ListItem, @Index - 1)))
  21. IF @Item != ''
  22. BEGIN
  23. INSERTINTO @Tab(Item)
  24. VALUES(CAST(@Item ASVARCHAR(MAX)))
  25. END
  26. SET @ListItem = RIGHT(@ListItem, LEN(@ListItem) - @Index)
  27. SET @Index = CHARINDEX(@Delimiter, @ListItem, 1)
  28. END
In the above function we are taking two parameters from the user. The first parameter defines the list and the second parameter defines delimiter. Return type of function is table. We return a table that contains two column. The first column of table contains a unique id for each item and the second column contains the items that are obtained after the conversion.

Let us take some examples.

Example 1
  1. DECLARE @List [nvarchar](max);
  2. DECLARE @Delim [nvarchar](max);
  3. SET @List='Rajastahn,Haryana,Delhi,Punjab,Goa,J&K,Chhattisgarh,Bihar';
  4. SET @Delim=',';
  5. SELECT Item AS [State]
  6. FROM dbo.UDF_ConvertListItem2Table(@List,@Delim);
Output

output
Example 2
  1. DECLARE @List [nvarchar](max);
  2. DECLARE @Delim [nvarchar](max);
  3. SET @List='Hyderabad*Itangar*Patna*Raipur*Panaji*J&Thiruvananthapuram*Aizawi*Jaipur';
  4. SET @Delim='*';
  5. SELECT Item AS [State]
  6. FROM dbo.UDF_ConvertListItem2Table(@List,@Delim);
Output

Output

Example 3
  1. DECLARE @State[nvarchar](max);
  2. DECLARE @Capital[nvarchar](max);
  3. DECLARE @Delim[nvarchar](max);
  4. SET @State = 'AndraPradesh,ArunachalPradesh,Bihar,Chhattisgarh,Goa,Kerala,Mizoram,Rajasthan'
  5. SET @Capital = 'Hyderabad,Itangar,Patna,Raipur,Panaji,Thiruvananthapuram,Aizawi,Jaipur';
  6. SET @Delim = ',';
  7. SELECTtab.ItemAS[State], Tab2.[Capital] AS[Capital]
  8. FROM dbo.UDF_ConvertListItem2Table(@State, @Delim) AS tab
  9. CROSSAPPLY
  10. (SELECT tab1.Item AS[Capital] FROM dbo.UDF_ConvertListItem2Table(@Capital, @Delim) AS tab1 WHEREtab.Id = tab1.Id) Tab2
Output

Output

Convert Table Into List

To convert a table into a list we have multiple choices, like using the COALESCE, SELECT or like CURSOR. We will discuss all these methods today. We can use any method to convert a table into a list, but we should prefer the COALESCE method.

Method 1: (Using COALESCE)

Firstly, we create a table that contains the names of states.
  1. CREATE TABLE# Temp
  2. (
  3. [State][nvarchar](max)
  4. );
  6. INSERT INTO# Temp
  7. SELECT 'Andra Pradesh'
  8. UNIONALL
  9. SELECT 'Arunachal Pradesh'
  10. UNIONALL
  11. SELECT 'Bihar'
  12. UNIONALL
  13. SELECT 'Chhattisgarh'
  14. UNIONALL
  15. SELECT 'Goa'
  16. UNIONALL
  17. SELECT 'Kerala'
  18. UNIONALL
  19. SELECT 'Mizoram'
  20. UNIONALL
  21. SELECT 'Rajasthan'
  23. SELECT * FROM# Temp AS[#TE]
Output

Output
Now we create a list using COALESCE for above table, all elements will be separated by commas.
  1. DECLARE @List [nvarchar](max);
  2. SELECT @List=COALESCE(@List+',','')+[State]
  3. FROM #Temp;
  4. SELECT @List AS [ListOfSates];
Output

Output

Method 2: (Using SELECT)
  1. DECLARE @List [nvarchar](max);
  2. SET @List='';
  3. SELECT @List=@List+[State]+','
  4. FROM #Temp;
  5. SELECTSUBSTRING(@List,1,LEN(@List)-1)AS [ListOfSates];
Output

Output

Method 3: (Using Cursor)
  1. DECLARE @State[nvarchar](max);
  2. DECLARE @StateText[nvarchar](max);
  3. SET @StateText = '';
  4. DECLARESelect_CursorCURSOR
  5. LOCALFORWARD_ONLYFOR
  6. SELECT * FROM# Temp AS# TE
  7. OPENSelect_Cursor
  8. FETCHNEXTFROMSelect_CursorINTO @State
  9. WHILE @ @FETCH_STATUS = 0
  10. BEGIN
  11. SET @StateText = @StateText + @State + ',';
  12. FETCHNEXTFROMSelect_CursorINTO @State;
  13. END
  14. CLOSESelect_Cursor
  15. DEALLOCATESelect_Cursor
  17. SELECTSUBSTRING(@StateText, 1, LEN(@StateText) - 1) AS[State];
Output

Output

We can convert a table into a list using a cursor but it is not an appropriate way because a cursor requires more resources compared to another method and it makes the process slow. So never select a cursor for the conversion of a table into a list.

Thanks for reading the article.