August 10, 2015 at 3:46 am
Hi,
I have requirement to list each row into column. I have tried with pivot query but unable to get it. Can you please help. I am using SQL Server 2008 database.
Here is the sample data:
CREATE TABLE dbo.test (
action_id numeric,
action VARCHAR(20) NOT NULL,
action_by VARCHAR(20) NOT NULL,
action_date VARCHAR(20) NOT NULL
);
INSERT INTO dbo.test (action_id,action, action_by,action_date)
VALUES (1,'Action1', 'xxxx','12/31/2015'),
(1,'Action2', 'xxxx','12/28/2015'),
(1,'Action3', 'yyyy','12/29/2015')
Output required as follows:
----------------------------
action1
xxxx
12/31/2015
---------
action2
xxxxx
12/28/2015
---------
action3
yyyy
12/29/2015
Thanks
NLV
August 10, 2015 at 4:05 am
Quick suggestion
😎
USE tempdb;
GO
SET NOCOUNT ON;
IF OBJECT_ID(N'dbo.test') IS NOT NULL DROP TABLE dbo.test;
GO
CREATE TABLE dbo.test (
action_id numeric,
action VARCHAR(20) NOT NULL,
action_by VARCHAR(20) NOT NULL,
action_date VARCHAR(20) NOT NULL
);
INSERT INTO dbo.test (action_id,action, action_by,action_date)
VALUES (1,'Action1', 'xxxx','12/31/2015'),
(1,'Action2', 'xxxx','12/28/2015'),
(1,'Action3', 'yyyy','12/29/2015');
SELECT
X.OUT_COL
FROM dbo.test T
CROSS APPLY
(
SELECT CONVERT(VARCHAR(50),action_id,0) UNION ALL
SELECT action UNION ALL
SELECT action_by UNION ALL
SELECT action_date
) AS X(OUT_COL)
;
Results
OUT_COL
------------
1
Action1
xxxx
12/31/2015
1
Action2
xxxx
12/28/2015
1
Action3
yyyy
12/29/2015
August 10, 2015 at 4:18 am
Thanks Erikson for your help.
August 10, 2015 at 4:22 am
NLV (8/10/2015)
Thanks Erikson for your help.
You are very welcome
😎
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply