How Can I Show The Columns Data Using Pivot

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

, +

Hi,
I have a table with multiple columns. For which i need to show every column into single row itself. including the id.
my table:
Id|| Name ||Desc || comments
1 || abc|| abcd || abcdab
2 || efg || adkfj || ajfkjdsalk
3 || aaa || dklasf || kdfjdkjfkjdk
so on
i want to show every column in single row.
Req Output:
id1 ||id2 ||id3 ||Name1 ||Name2 ||Name3 ||Comeents1 ||comments2 ||comments3
1 || 2 || 3 || abc || efg || aaa || abcdab || ajfkjdsalk || kdfjdkjfkjdk

i have tried using pivot but unable to display this output. Can you suggest me if pivot is an appropriate technique to use or is there someother way to display the data.?
--
Thanks in Advance. :)

Posted 2-Jul-15 2:59am

pn46

Updated 2-Jul-15 3:12am

v3

Add a Solution

Comments

virusstorm 2-Jul-15 10:33am

So this is doable, you will need to use dynamic SQL to achieve this, but I have to ask why on earth would you need to do this? The solution can quickly grow out of control and cause a great deal of issues. Assuming your table is only four columns, after 30 rows you are returning 120 columns!

pn46 3-Jul-15 5:34am

Hi,
Thanks for the reply. but i had a need to show the report this way. (4*3) = 12 colums. since this is the requirement. I have tried to put the values in temp variable and set the values into a live table. Since the lines of code are increasing by doing so, hence, I was wondering if ther is any other way to fetch the data individually for my requirement.
Thanks :)

virusstorm 3-Jul-15 19:16pm

Is it always fixed as a 4x3 table?

pn46 6-Jul-15 1:33am

Hi,
Yes, the table I am maintaining is fixed with certain cols. 4*3 is an example i had asked here. actually my table contains 8*6cols ,i.e., 8 rows and 6 cols. I need to show all the 8 rows of 6cols as in single row. Hope you have got my point.
Thanks in Advance. :)

1 solution

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)

virusstorm · Accepted Answer · 2015-07-06T02:22:00

Assume this is our table:

SQL

CREATE TABLE dbo.SomeTable
(
	Id INT NOT NULL,
	Name VARCHAR(100) NOT NULL,
	[Description] NVARCHAR(max) NULL,
	Comments NVARCHAR(max) NULL,
	CONSTRAINT PK_SomeTable PRIMARY KEY (Id)
);

If the number of rows and columns is always fixed, this example will work:

SQL

DECLARE @flatenedTable TABLE
(
	Id_1 INT NULL,
	Id_2 INT NULL,
	Id_3 INT NULL,
	Id_4 INT NULL,
	Name_1 VARCHAR(100) NULL,
	Name_2 VARCHAR(100) NULL,
	Name_3 VARCHAR(100) NULL,
	Name_4 VARCHAR(100) NULL,
	Description_1 VARCHAR(MAX) NULL,
	Description_2 VARCHAR(MAX) NULL,
	Description_3 VARCHAR(MAX) NULL,
	Description_4 VARCHAR(MAX) NULL,
	Comments_1 VARCHAR(MAX) NULL,
	Comments_2 VARCHAR(MAX) NULL,
	Comments_3 VARCHAR(MAX) NULL,
	Comments_4 VARCHAR(MAX) NULL
);

DECLARE @startingId INT

SELECT
	@startingId = MIN(Id)
FROM dbo.SomeTable

INSERT INTO @flatenedTable (
	Id_1,
	Id_2,
	Id_3,
	Id_4,
	Name_1,
	Name_2,
	Name_3,
	Name_4,
	Description_1,
	Description_2,
	Description_3,
	Description_4,
	Comments_1,
	Comments_2,
	Comments_3,
	Comments_4
)
SELECT
	CASE WHEN Id = @startingId + 0 THEN Id ELSE NULL END AS Id_1,
	CASE WHEN Id = @startingId + 1 THEN Id ELSE NULL END AS Id_2,
	CASE WHEN Id = @startingId + 2 THEN Id ELSE NULL END AS Id_3,
	CASE WHEN Id = @startingId + 3 THEN Id ELSE NULL END AS Id_4,
	CASE WHEN Id = @startingId + 0 THEN Name ELSE NULL END AS Name_1,
	CASE WHEN Id = @startingId + 1 THEN Name ELSE NULL END AS Name_2,
	CASE WHEN Id = @startingId + 2 THEN Name ELSE NULL END AS Name_3,
	CASE WHEN Id = @startingId + 3 THEN Name ELSE NULL END AS Name_4,
	CASE WHEN Id = @startingId + 0 THEN [Description] ELSE NULL END AS Description_1,
	CASE WHEN Id = @startingId + 1 THEN [Description] ELSE NULL END AS Description_2,
	CASE WHEN Id = @startingId + 2 THEN [Description] ELSE NULL END AS Description_3,
	CASE WHEN Id = @startingId + 3 THEN [Description] ELSE NULL END AS Description_4,
	CASE WHEN Id = @startingId + 0 THEN [Comments] ELSE NULL END AS Comments_1,
	CASE WHEN Id = @startingId + 1 THEN [Comments] ELSE NULL END AS Comments_2,
	CASE WHEN Id = @startingId + 2 THEN [Comments] ELSE NULL END AS Comments_3,
	CASE WHEN Id = @startingId + 3 THEN [Comments] ELSE NULL END AS Comments_4
FROM dbo.SomeTable

SELECT
	MAX(Id_1) AS Id_1,
	MAX(Id_2) AS Id_2,
	MAX(Id_3) AS Id_3,
	MAX(Id_4) AS Id_4,
	MAX(Name_1) AS Name_1,
	MAX(Name_2) AS Name_2,
	MAX(Name_3) AS Name_3,
	MAX(Name_4) AS Name_4,
	MAX(Description_1) AS Description_1,
	MAX(Description_2) AS Description_2,
	MAX(Description_3) AS Description_3,
	MAX(Description_4) AS Description_4,
	MAX(Comments_1) AS Comments_1,
	MAX(Comments_2) AS Comments_2,
	MAX(Comments_3) AS Comments_3,
	MAX(Comments_4) AS Comments_4
FROM @flatenedTable

However, if they are not fixed, you will need to used dynamic SQL to achieve this:

SQL

DECLARE @numberOfRows INT;
DECLARE @index INT = 1;
DECLARE @newLine CHAR(2) = CHAR(13) + CHAR(10);

SELECT
	@numberOfRows = COUNT(*)
FROM dbo.SomeTable

DECLARE @variableTableSql NVARCHAR(MAX) = N'DECLARE @flatenedTable TABLE' + @newLine + '(' + @newLine;
DECLARE @selectStatement NVARCHAR(MAX) = N'SELECT' + @newLine;
DECLARE @aggregateSelect NVARCHAR(MAX) = N'SELECT' + @newLine;

WHILE @index <= @numberOfRows
BEGIN
	SET @variableTableSql = CONCAT(@variableTableSql,
		CHAR(9), N'Id_', @index, N' INT NULL,', @newLine,
		CHAR(9), N'Name_', @index, N' VARCHAR(100) NULL,', @newLine,
		CHAR(9), N'Description_', @index, N' VARCHAR(MAX) NULL,' + @newLine,
		CHAR(9), N'Comments_', @index, N' VARCHAR(MAX) NULL');

	SET @selectStatement = CONCAT(@selectStatement,
		CHAR(9), N'CASE WHEN Id = ', @index, N' THEN Id ELSE NULL END AS Id_', @index, N',', @newLine,
		CHAR(9), N'CASE WHEN Id = ', @index, N' THEN Name ELSE NULL END AS Name_', @index, N',', @newLine,
		CHAR(9), N'CASE WHEN Id = ', @index, N' THEN [Description] ELSE NULL END AS Description_', @index, N',', @newLine,
		CHAR(9), N'CASE WHEN Id = ', @index, N' THEN [Comments] ELSE NULL END AS Comments_', @index);

	SET @aggregateSelect = CONCAT(@aggregateSelect,
		CHAR(9), 'MAX(Id_', @index, N') AS Id_', @index, N',', @newLine,
		CHAR(9), 'MAX(Name_', @index, N') AS Name_', @index, N',', @newLine,
		CHAR(9), 'MAX(Description_', @index, N') AS Description_', @index, N',', @newLine,
		CHAR(9), 'MAX(Comments_', @index, N') AS Comments_', @index);

	IF @index != @numberOfRows
	BEGIN
		SET @variableTableSql = CONCAT(@variableTableSql, N',', @newLine);
		SET @selectStatement = CONCAT(@selectStatement, N',', @newLine);
		SET @aggregateSelect = CONCAT(@aggregateSelect, N',', @newLine);
	END
	ELSE
	BEGIN
		SET @variableTableSql = CONCAT(@variableTableSql, @newLine, ');');
		SET @selectStatement = CONCAT(@selectStatement, @newLine, 'FROM dbo.SomeTable');
		SET @aggregateSelect = CONCAT(@aggregateSelect, @newLine, 'FROM @flatenedTable');
	END

	SET @index = @index + 1
END

--DEBUG purposes
--PRINT @variableTableSql;
--PRINT @selectStatement;

DECLARE @sql NVARCHAR(MAX);

SET @sql = CONCAT(
	@variableTableSql,
	@newLine,
	@newLine,
	'INSERT INTO @flatenedTable',
	@newLine,
	@selectStatement,
	@newLine,
	@newLine,
	@aggregateSelect);

--DEBUG purpose
--PRINT @sql

EXEC sp_executesql @sql