Show Columns When Based On Row Values

Transact-SQL
1

hi

I have difficulty to select only columns that do not contain the row value "total" which is very dynamic meaning the value "total" can appear in any columns based on the CSV file which imported into a table. Here is the sample table data:

COL1 COL2 COL3 COL4 COL5 COL6
total 10 100 total total 55
100 12 10 15 12 56
105 15 102 102 16 40
111 55 12 200 100 10

How do i select columns that does not contain the row value "total" and the result set would be this:
COL2 COL3 COL6

Thanks a lot

2

Hi

I was able to do it this way ..

I hope it helps ..

/*
DROP TABLE #abc

CREATE TABLE #abc
(COL1 varchar(100) NULL,
COL2 varchar(100) NULL,
COL3 varchar(100) NULL,
COL4 varchar(100) NULL,
COL5 varchar(100) NULL,
COL6 varchar(100) NULL )
INSERT INTO #abc select 'total','10','100','total','total','55'

INSERT INTO #abc select '100','12','10','15','12','56'

INSERT INTO #abc select '105','15','102','102','16','40'

INSERT INTO #abc select '111','55','12','200','100','10'

*/
DECLARE @Names VARCHAR(8000)

SELECT @Names = COALESCE(@Names + ', ', '') + a.X
FROM (
SELECT CASE
WHEN (count(col1) > 0)
THEN ''
ELSE 'col1'
END AS x
FROM #abc
WHERE col1 LIKE 'total'

UNION

SELECT CASE 
		WHEN (count(col2) > 0)
			THEN ''
		ELSE 'col2'
		END
FROM #abc
WHERE col2 LIKE 'total'

UNION

SELECT CASE 
		WHEN (count(col3) > 0)
			THEN ''
		ELSE 'col3'
		END
FROM #abc
WHERE col3 LIKE 'total'

UNION

SELECT CASE 
		WHEN (count(col4) > 0)
			THEN ''
		ELSE 'col4'
		END
FROM #abc
WHERE col4 LIKE 'total'

UNION

SELECT CASE 
		WHEN (count(col5) > 0)
			THEN ''
		ELSE 'col5'
		END
FROM #abc
WHERE col5 LIKE 'total'

UNION

SELECT CASE 
		WHEN (count(col6) > 0)
			THEN ''
		ELSE 'col6'
		END
FROM #abc
WHERE col6 LIKE 'total'
) a

SELECT @Names = 'select ' + @Names + ' from #abc'

SELECT @Names = replace(@Names, 'select ,', 'select ')

EXEC (@names)

3

Hi I am pasting again .. with better formatting

/*
DROP TABLE #abc

CREATE TABLE #abc
(COL1 varchar(100) NULL,
COL2 varchar(100) NULL,
COL3 varchar(100) NULL,
COL4 varchar(100) NULL,
COL5 varchar(100) NULL,
COL6 varchar(100) NULL)

INSERT INTO #abc select 'total','10','100','total','total','55'
INSERT INTO #abc select '100','12','10','15','12','56'
INSERT INTO #abc select '105','15','102','102','16','40'
INSERT INTO #abc select '111','55','12','200','100','10'

*/
DECLARE @Names VARCHAR(8000)

SELECT @Names = COALESCE(@Names + ', ', '') + a.X
FROM (
SELECT CASE WHEN (count(col1) > 0) THEN '' ELSE 'col1' END AS x FROM #abc
WHERE col1 LIKE 'total'
UNION
SELECT CASE WHEN (count(col2) > 0) THEN '' ELSE 'col2' END FROM #abc
WHERE col2 LIKE 'total'
UNION
SELECT CASE WHEN (count(col3) > 0) THEN '' ELSE 'col3' END FROM #abc
WHERE col3 LIKE 'total'
UNION
SELECT CASE WHEN (count(col4) > 0) THEN '' ELSE 'col4' END FROM #abc
WHERE col4 LIKE 'total'
UNION
SELECT CASE WHEN (count(col5) > 0) THEN '' ELSE 'col5' END FROM #abc
WHERE col5 LIKE 'total'
UNION
SELECT CASE WHEN (count(col6) > 0) THEN '' ELSE 'col6' END FROM #abc
WHERE col6 LIKE 'total'
) a

SELECT @Names = 'select ' + @Names + ' from #abc'
SELECT @Names = replace(@Names, 'select ,', 'select ')
EXEC (@names)

4 
SELECT
    STUFF(
    CASE WHEN COL1_total = 0 THEN ',COL1' ELSE '' END +
    CASE WHEN COL2_total = 0 THEN ',COL2' ELSE '' END +
    CASE WHEN COL3_total = 0 THEN ',COL3' ELSE '' END +
    CASE WHEN COL4_total = 0 THEN ',COL4' ELSE '' END +
    CASE WHEN COL5_total = 0 THEN ',COL5' ELSE '' END +
    CASE WHEN COL6_total = 0 THEN ',COL6' ELSE '' END
    , 1, 1, '') AS Col_List
FROM (
    SELECT 
        SUM(CASE WHEN COL1 = 'total' THEN 1 ELSE 0 END) AS COL1_total,
        SUM(CASE WHEN COL2 = 'total' THEN 1 ELSE 0 END) AS COL2_total,
        SUM(CASE WHEN COL3 = 'total' THEN 1 ELSE 0 END) AS COL3_total,
        SUM(CASE WHEN COL4 = 'total' THEN 1 ELSE 0 END) AS COL4_total,
        SUM(CASE WHEN COL5 = 'total' THEN 1 ELSE 0 END) AS COL5_total,
        SUM(CASE WHEN COL6 = 'total' THEN 1 ELSE 0 END) AS COL6_total
    FROM #abc
) AS derived
1 Like
5

Not seen STUFF used like that (to remove a leading comma). Handy :smile:

6

Hi I posted above .. posting the same thing again with nice formatting

DECLARE @Names VARCHAR(8000)

SELECT @Names = COALESCE(@Names + ', ', '') + a.X
FROM (
	SELECT CASE 
			WHEN (count(col1) > 0)
				THEN ''
			ELSE 'col1'
			END AS x
	FROM #abc
	WHERE col1 LIKE 'total'
	
	UNION
	
	SELECT CASE 
			WHEN (count(col2) > 0)
				THEN ''
			ELSE 'col2'
			END
	FROM #abc
	WHERE col2 LIKE 'total'
	
	UNION
	
	SELECT CASE 
			WHEN (count(col3) > 0)
				THEN ''
			ELSE 'col3'
			END
	FROM #abc
	WHERE col3 LIKE 'total'
	
	UNION
	
	SELECT CASE 
			WHEN (count(col4) > 0)
				THEN ''
			ELSE 'col4'
			END
	FROM #abc
	WHERE col4 LIKE 'total'
	
	UNION
	
	SELECT CASE 
			WHEN (count(col5) > 0)
				THEN ''
			ELSE 'col5'
			END
	FROM #abc
	WHERE col5 LIKE 'total'
	
	UNION
	
	SELECT CASE 
			WHEN (count(col6) > 0)
				THEN ''
			ELSE 'col6'
			END
	FROM #abc
	WHERE col6 LIKE 'total'
	) a

SELECT @Names = 'select ' + @Names + ' from #abc'

SELECT @Names = replace(@Names, 'select ,', 'select ')

EXEC (@names)

/*
DROP TABLE #abc

CREATE TABLE #abc
(COL1 varchar(100) NULL,
COL2 varchar(100) NULL,
COL3 varchar(100) NULL,
COL4 varchar(100) NULL,
COL5 varchar(100) NULL,
COL6 varchar(100) NULL)

INSERT INTO #abc select 'total','10','100','total','total','55'
INSERT INTO #abc select '100','12','10','15','12','56'
INSERT INTO #abc select '105','15','102','102','16','40'
INSERT INTO #abc select '111','55','12','200','100','10'

*/