Recursive search and horizontal appending

1

Hello

I am looking to:
Get all the fields of a View/Table from information_schema
Search for '%something%' in each of these fields
Put the results of each of these subqueries one next to the other
Since the length of the results lists may not be the same, I would like to populate any rows needed with null

So let's say we have a table:
Col1, Col2

I want to search for '%something%' in Col1 and Col2 etc
Let's say searching Col1 returns:
Something1
Something2
Col2 returns nothing
Col3 returns:
Something10
Something20
Something30

So the end result table should be:

Col1 Col2 Col3
Something1 NULL Something10
Something2 NULL Something20
NULL NULL Something30

Any idea how to do that? I would really need something neater than me repeating all the columns of the table one by one. Something like recursive search and horizontal appending.

Thanks!

2

I think you can do that based on the ORDINAL_POSITION. This query will look in the first four positions, you should expand it to the max amount.

SELECT
TableName,
[1],
[2],
[3],
[4]
FROM
(
SELECT COLUMN_NAME AS 'ColumnName'
,TABLE_NAME AS 'TableName'
, ORDINAL_POSITION
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE '%Store%'
) PVT
PIVOT
(MAX(ColumnName) FOR ORDINAL_POSITION IN ([1],[2],[3], [4])) Tbl

3

I think OP wants the data in the table by searching on each column?

4

I think one approach might be to do this dynamically. My question to you though is what are you trying to solve, why is it that you have to do this? Are you doing some sort of data forensics? Also I believe you have asked this question in the past. Did the answers there not work for you?

5

The following example demonstrates a way to do it. I honestly don't think it's useful output, because it's not correlating anything between rows. I think you'd be better off with a straight UNPIVOT output.

create table #(col1 varchar(20) null, col2 varchar(20) null, col3 varchar(20) null, col4 varchar(20) null);
-- run the following INSERT a few times to get data
insert #(col1,col2,col3,col4) select
case when CHECKSUM(newid())%5<2 then null else 'something'+left(cast(newid() as varchar(36)),2) end
,case when CHECKSUM(newid())%5<2 then null else 'something'+left(cast(newid() as varchar(36)),4) end
,case when CHECKSUM(newid())%5<2 then null else left(cast(newid() as varchar(36)),3)+'something' end
,case when CHECKSUM(newid())%5<2 then null else left(cast(newid() as varchar(36)),2)+'something' end

-- this is the query to create the output
;with cte as (select val,col, DENSE_RANK() over (partition by col order by val) rn  -- generate row number for each col
from (select * from #) a  -- basic query to unpivot
unpivot(val for col in (col1,col2,col3,col4)) b
where val like '%something%'
)
select 
col1,col2,col3,col4  -- get pivoted columns
from cte
pivot(max(val) for col in (col1,col2,col3,col4)) c
order by rn  -- "group" by each row number, null if there aren't that many

The CHECKSUM(newid()) stuff is just to generate random NULLs in each column. You need DENSE_RANK instead of ROW_NUMBER in case there are duplicates (my sample data generator doesn't create dupes, at least not in my testing).

I'm pretty sure this can be combined with @RogierPronk 's query to generate the necessary SQL for all of your tables, my brain just isn't up for it at the moment.

2 Likes