How to arrange row without null value.

Question

3.50/5 (2 votes)

See more:

SQL-Server-2008R2

I have a generate color table as I mention below with pivote methods

SQL

color1     color 2
Green       --
yellow      --
--          Black
--          White

I want to show this

XML

color1     color 2
Green       Black
yellow      White

Please help to resolve it

Posted 30-Jun-14 20:14pm

DINESH K MAURYA

Add a Solution

Comments

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ) 1-Jul-14 2:19am

What have you tried?

DINESH K MAURYA 1-Jul-14 2:26am

I have a table xyz which row is
colortype colorname
--------------
color1 Green
color2 black
color1 yelow
color1 Pink
color2 white

I have pivot it
and reslut as in question but i want to show like
color1 color 2
__________________
Green Black
yellow White

I have show in gridview it

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Magic Wonder · Answer 1 · 2014-07-01T20:33:00

Hi,

Try this.

SQL

create table myColour
(
color1 varchar(20),
color2 varchar(20),
)

INSERT INTO myColour (color1) values ('White')
INSERT INTO myColour (color1) values ('Black')
INSERT INTO myColour (color2) values ('Yellow')
INSERT INTO myColour (color2) values ('Green')

SELECT * FROM myColour


--exec getMyColour
CREATE PROC getMyColour
AS
BEGIN

CREATE TABLE #tmpTableA(
	[ID] [INT] IDENTITY(1,1),
	[color1] [varchar](20)
)

INSERT INTO #tmpTableA SELECT color1 from myColour where isnull(color1,'') <> ''

CREATE TABLE #tmpTableB(
	[ID] [INT] IDENTITY(1,1),
	[color2] [varchar](20)
)

INSERT INTO #tmpTableB SELECT color2 from myColour where isnull(color2,'') <> ''


SELECT [color1],[color2] from #tmpTableA A, #tmpTableB B
where A.id=B.id

END

Hope this will help you.

Cheers.

Andrius Leonavicius · Answer 2 · 2014-07-01T20:05:00

Solution 1

Hi,

You can do it in this way:

SQL

CREATE TABLE #TempColors (Color1 VARCHAR(50), Color2 VARCHAR(50));
 
INSERT INTO #TempColors (Color1, Color2)
VALUES ('Green', NULL), ('Yellow', NULL), (NULL, 'Black'), (NULL, 'White'), ('Pink', NULL);
 
SELECT Color1, Color2 FROM #TempColors;
 

;WITH IDs_CTE
     AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS ID
         FROM #TempColors),
     Color1_CTE
     AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS ID, Color1
         FROM #TempColors
         WHERE Color1 IS NOT NULL),
     Color2_CTE
     AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS ID, Color2
         FROM #TempColors
         WHERE Color2 IS NOT NULL)
SELECT Color1_CTE.Color1, Color2_CTE.Color2
FROM   IDs_CTE
       LEFT OUTER JOIN Color1_CTE
                    ON Color1_CTE.ID = IDs_CTE.ID
       LEFT OUTER JOIN Color2_CTE
                    ON Color2_CTE.ID = IDs_CTE.ID
WHERE  Color1_CTE.Color1 IS NOT NULL OR Color2_CTE.Color2 IS NOT NULL
 

DROP TABLE #TempColors;

Posted 1-Jul-14 20:05pm

Andrius Leonavicius

Updated 1-Jul-14 20:27pm

v2

Comments

DINESH K MAURYA 2-Jul-14 2:30am

Thanks for reply
If the color1 and color2 are dynamic then how to do this

Andrius Leonavicius 2-Jul-14 2:43am

Do you mean that it could be color3, color4, etc.?

DINESH K MAURYA 2-Jul-14 2:44am

Yes may be increase or descress
because i have generate this table after pivot

Andrius Leonavicius 2-Jul-14 3:40am

OK, I get it now. I gave you a dynamic pivot solution recently: Transpose query result[^]. If you're using it, then you can easily rearrange values. For example, change
ORDER BY Attribute_SubID
to
ORDER BY Attribute_Sub DESC
and after
[...] AS RowNumber
FROM #DataTable:
add this line:
WHERE Attribute_Sub <> ''--''

DINESH K MAURYA 2-Jul-14 5:08am

As you suggest that I have change accordingly
blow modified code, but same result


CREATE TABLE #DataTable (Attribute_SubID INT, AttributeID INT, Attribute_Sub NVARCHAR(50));

INSERT INTO #DataTable (Attribute_SubID, AttributeID, Attribute_Sub) VALUES (3, 2, 'xyz'), (4, 2, 'abc'), (3, 2, 'def');

 
DECLARE @ColumnsTable TABLE (Col VARCHAR(10));

INSERT INTO @ColumnsTable (Col)
SELECT DISTINCT Attribute_SubID FROM #DataTable;

DECLARE @SQL VARCHAR(MAX);

SET @SQL = ';WITH Data
AS
(
SELECT Attribute_SubID, Attribute_Sub, ROW_NUMBER() OVER (PARTITION BY Attribute_SubID
ORDER BY Attribute_Sub desc) AS RowNumber
FROM #DataTable
where Attribute_Sub <> ''--''
)
SELECT ' + (SELECT STUFF((SELECT ', ISNULL([' + Col + '], ''--'') AS [' + Col + ']'
FROM @ColumnsTable
FOR XML PATH('')), 1, 2, '')) +
'
FROM Data
PIVOT
(
MAX(Attribute_Sub)
FOR Attribute_SubID IN
(' + (SELECT STUFF((SELECT ', [' + Col + ']'
FROM @ColumnsTable
FOR XML PATH('')), 1, 2, '')) + ')
) AS pvt;';

--SELECT @SQL;
 
EXEC(@SQL);

 
DROP TABLE #DataTable;

Andrius Leonavicius 2-Jul-14 5:21am

Really? Have you tried it with your data? I tried with the following data:
(3, 2, 'xyz'), (4, 2, 'abc'), (3, 2, NULL), (4, 2, NULL), (4, 2, '2'), (3, 2, '3')

Here's the difference:
3 4
xyz abc
3 2

versus

3 4
xyz --
-- 2
3 abc

DINESH K MAURYA 2-Jul-14 5:34am

Thanks alot

Andrius Leonavicius 2-Jul-14 5:37am

You're welcome.

Andrius Leonavicius 2-Jul-14 4:16am

Also, it would be nice if you would accept valuable answers (and rate them). :)