November 13, 2014 at 2:22 pm
I have 5 columns in my database. 1 column is coming like a dynamic.
I want to convert records from rows to columns. Currently I have a data like this.
Race AgeRange Amount
W 17-20 500
W 21-30 400
W 31-40 200
A 17-20 100
H 41-50 250
H 51-60 290
So age range is not fixed and it can be any and I have one separate relational table for age range where it's coming from. Now I want to convert it into columns like
Race 17-20 21-30 31-40 41-50 51-60
W 500 400 200 0 0
A 100 0 0 0 0
H 0 0 0 250 290
Thanks.
November 13, 2014 at 2:31 pm
DECLARE @t TABLE(Race char(1), AgeRange char(5), Amount int)
INSERT INTO @t (Race, AgeRange, Amount) values
('W','17-20',5000),
('W','21-30',4000),
('W','31-40',2000),
('A','17-20',1000),
('H','41-50',2500),
('H','51-60',2900)
select Race,
isnull("17-20",0),
isnull("21-30",0),
isnull("31-40",0),
isnull("41-50",0),
isnull("51-60",0)
from @t t
pivot (max(Amount) for AgeRange in ("17-20", "21-30", "31-40", "41-50", "51-60") ) p
Gerald Britton, Pluralsight courses
November 13, 2014 at 3:16 pm
You could also try cross tabs. Read the following articles on how to work with them and how to make them dynamic.
Part 1: http://www.sqlservercentral.com/articles/T-SQL/63681/
Part 2: http://www.sqlservercentral.com/articles/Crosstab/65048/
November 14, 2014 at 8:15 am
Quick suggestion for a dynamic cross-tab solution
😎
USE tempdb;
GO
SET NOCOUNT ON;
IF OBJECT_ID('dbo.TBL_SAMPLE_DATA') IS NOT NULL DROP TABLE dbo.TBL_SAMPLE_DATA;
CREATE TABLE dbo.TBL_SAMPLE_DATA
(
Race CHAR(1) NOT NULL
,AgeRange CHAR(5) NOT NULL
,Amount NUMERIC(10,0) NOT NULL
)
INSERT INTO dbo.TBL_SAMPLE_DATA (Race, AgeRange, Amount)
VALUES
('W','17-20',500)
,('W','21-30',400)
,('W','31-40',200)
,('A','17-20',100)
,('H','41-50',250)
,('H','51-60',290)
;
DECLARE @SQL_STR NVARCHAR(MAX) = N'SELECT
SD.Race
{{COL_SET}}
FROM dbo.TBL_SAMPLE_DATA SD
GROUP BY SD.Race;';
;WITH COL_LIST(COLNAME) AS
(
SELECT DISTINCT
SD.AgeRange AS COLNAME
FROM dbo.TBL_SAMPLE_DATA SD
)
,COL_SET AS
(
SELECT
CL.COLNAME
,ROW_NUMBER() OVER
(
PARTITION BY (SELECT NULL)
ORDER BY CL.COLNAME
) AS COL_POS
FROM COL_LIST CL
)
SELECT @SQL_STR = REPLACE(@SQL_STR,N'{{COL_SET}}',(
SELECT
NCHAR(9) + N',ISNULL(MAX(CASE WHEN SD.AgeRange = '
+ NCHAR(39) + CS.COLNAME + NCHAR(39) + N' THEN SD.Amount END),0) AS '
+ QUOTENAME(CS.COLNAME) + NCHAR(13) + NCHAR(10)
FROM COL_SET CS
FOR XML PATH(''),TYPE).value('.[1]','NVARCHAR(MAX)'));
EXEC (@SQL_STR);
Results
Race 17-20 21-30 31-40 41-50 51-60
---- ------ ------ ------ ------ ------
A 100 0 0 0 0
H 0 0 0 250 290
W 500 400 200 0 0
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply