July 28, 2010 at 6:13 am
I have this table multiple rows that I have to stretch out to one long row, insurance-related. Each policy has a number of occurrences (rows in this table) and each of these occurrences has a set of monetary values. I need all of these occurrences and their associated fields to be stretched out into one row. So Policy P1 has:
Occ ThisValue ThatValue OtherValue...
110025078
220060150
3100010065
435562599
... (up to 45)
And it has to become:
Occ ThisValue1 ThatValue1 OtherValue1 Occ ThisValue2 ThatValue2 OtherValue2 Occ3 ThisValue3 ThatValue3 OtherValue3
1 100 250 78 2 200 60 150 3 1000 100 65
The reason for this is that I have to create a text file from a SQL 2005 database. I'm using an SSIS package to create the file. I had been planning on using a view to present the data, ready for the package to export to the file. So, I was thinking a pivot table for the above problem but I can't make it work; could be because I've never had to use a pivot table before and maybe I'm just not doing it right.
Now I'm think I might want to solve the above problem in the package.
Thoughts?
Thanks in advance for your help!
July 28, 2010 at 6:17 am
By the way, sorry the data isn't formatted better...
July 28, 2010 at 5:46 pm
Based on the assumption you'e looking for a way to get the rows into separate columns, please have a look at the CrossTab article referenced in my signature.
July 29, 2010 at 1:40 pm
I had this same issue a while back when trying to produce a list of all the area code / prefix combinations in a given state. Same principle. Anyway, the idea is to convert them as one string into a memvar then doing something with it.
For my example, I assumed your columns were all integer, so I converted them... with string memvars you'll want to TRIM them.
----------------------------------------------------------------------
[font="Courier New"]DECLARE @Codes varchar(8000)
SET @Codes = ''
SELECT @Codes = @Codes + CONVERT(varchar, Occ) + ' ' +
CONVERT(varchar, ThisValue) + ' ' +
CONVERT(varchar, ThatValue) + ' ' +
CONVERT(varchar, OtherValue) + ' '
FROM YourTable
-- trim off the trailing space
SET @Codes = LEFT(@Codes, (LEN(@Codes) - 1))
-- show the magic
PRINT @Codes[/font]
----------------------------------------------------------------------
I hope that helps!
Jeff
July 29, 2010 at 2:21 pm
Thanks to both of your for your suggestions and time. It turned out to be MUCH more complicated than I realized.
For each row I have a key, a year (as an integer), an age and seven other columns of various data. Each key could have anywwhere from ~20 to ~90 rows.
Those rows have to be put into 45 columns, with 1 - 45 appended to the column name, one such row for each key. So:
key, year1, age1, <other col1s>, year2, age2, <other col2s> ... year45, age45, <other col45s>
The first 20 columns are the first 20 years (1 - 20, easy-peasy).
The next 15 rows are years 25+, counting by 5s.
The last 10 are ages 65 - 100 by 5s, then 120 and these have to be in predetermined colums; data for age 65 has to be in the set of "36" columns, age 70 in "37" columns. So if a key doesn't have an age 65 row, the "36" columns have to be null.
I have a big, fat, ugly but code with cursors and such, but it works. Fortunately the datasets aren't very large; hundreds of rows.
I'll try to find some time to condense and de-identify the code so I can post it. I'd like to find a more elegant (and less embarrassing from a code standpoint) solution.
Thanks! Hope that makes sense!
July 29, 2010 at 3:06 pm
I generated some data to use as a test ...
CREATE TABLE #TEST
(recIDINT PRIMARY KEY
,theKeyVARCHAR(10)
,theYearINT
,theValue INT)
INSERT INTO #TEST
SELECT 1,'key 2',1934,50 UNION ALL
SELECT 2,'key 2',1935,30 UNION ALL
SELECT 3,'key 2',1936,96 UNION ALL
SELECT 4,'key 2',1937,105 UNION ALL
SELECT 5,'key 2',1938,16 UNION ALL
SELECT 6,'key 2',1939,13 UNION ALL
SELECT 7,'key 2',1940,85 UNION ALL
SELECT 8,'key 2',1941,26 UNION ALL
SELECT 9,'key 2',1942,65 UNION ALL
SELECT 10,'key 2',1943,61 UNION ALL
SELECT 11,'key 2',1944,125 UNION ALL
SELECT 12,'key 2',1945,111 UNION ALL
SELECT 13,'key 2',1946,126 UNION ALL
SELECT 14,'key 2',1947,103 UNION ALL
SELECT 15,'key 1',1947,46 UNION ALL
SELECT 16,'key 1',1948,12 UNION ALL
SELECT 17,'key 2',1948,60 UNION ALL
SELECT 18,'key 2',1949,51 UNION ALL
SELECT 19,'key 1',1949,43 UNION ALL
SELECT 20,'key 2',1950,3 UNION ALL
SELECT 21,'key 1',1950,57 UNION ALL
SELECT 22,'key 1',1951,70 UNION ALL
SELECT 23,'key 2',1951,40 UNION ALL
SELECT 24,'key 2',1952,38 UNION ALL
SELECT 25,'key 1',1952,73 UNION ALL
SELECT 26,'key 2',1953,23 UNION ALL
SELECT 27,'key 1',1953,96 UNION ALL
SELECT 28,'key 2',1954,100 UNION ALL
SELECT 29,'key 1',1954,3 UNION ALL
SELECT 30,'key 2',1955,110 UNION ALL
SELECT 31,'key 1',1955,94 UNION ALL
SELECT 32,'key 1',1956,7 UNION ALL
SELECT 33,'key 2',1956,6 UNION ALL
SELECT 34,'key 1',1957,69 UNION ALL
SELECT 35,'key 2',1957,121 UNION ALL
SELECT 36,'key 2',1958,48 UNION ALL
SELECT 37,'key 1',1958,85 UNION ALL
SELECT 38,'key 1',1959,9 UNION ALL
SELECT 39,'key 2',1959,43 UNION ALL
SELECT 40,'key 2',1960,98 UNION ALL
SELECT 41,'key 3',1960,7 UNION ALL
SELECT 42,'key 1',1960,87 UNION ALL
SELECT 43,'key 1',1961,48 UNION ALL
SELECT 44,'key 3',1961,66 UNION ALL
SELECT 45,'key 2',1961,93 UNION ALL
SELECT 46,'key 1',1962,67 UNION ALL
SELECT 47,'key 3',1962,10 UNION ALL
SELECT 48,'key 2',1962,45 UNION ALL
SELECT 49,'key 2',1963,66 UNION ALL
SELECT 50,'key 3',1963,71 UNION ALL
SELECT 51,'key 1',1963,52 UNION ALL
SELECT 52,'key 2',1964,86 UNION ALL
SELECT 53,'key 3',1964,12 UNION ALL
SELECT 54,'key 1',1964,13 UNION ALL
SELECT 55,'key 2',1965,118 UNION ALL
SELECT 56,'key 3',1965,84 UNION ALL
SELECT 57,'key 1',1965,15 UNION ALL
SELECT 58,'key 3',1966,19 UNION ALL
SELECT 59,'key 2',1966,8 UNION ALL
SELECT 60,'key 1',1966,60 UNION ALL
SELECT 61,'key 1',1967,55 UNION ALL
SELECT 62,'key 3',1967,61 UNION ALL
SELECT 63,'key 2',1967,53 UNION ALL
SELECT 64,'key 1',1968,37 UNION ALL
SELECT 65,'key 3',1968,87 UNION ALL
SELECT 66,'key 2',1968,21 UNION ALL
SELECT 67,'key 1',1969,78 UNION ALL
SELECT 68,'key 3',1969,57 UNION ALL
SELECT 69,'key 2',1969,58 UNION ALL
SELECT 70,'key 1',1970,1 UNION ALL
SELECT 71,'key 2',1970,113 UNION ALL
SELECT 72,'key 3',1970,29 UNION ALL
SELECT 73,'key 2',1971,11 UNION ALL
SELECT 74,'key 1',1971,31 UNION ALL
SELECT 75,'key 3',1971,49 UNION ALL
SELECT 76,'key 2',1972,108 UNION ALL
SELECT 77,'key 1',1972,36 UNION ALL
SELECT 78,'key 3',1972,5 UNION ALL
SELECT 79,'key 1',1973,6 UNION ALL
SELECT 80,'key 2',1973,95 UNION ALL
SELECT 81,'key 3',1973,14 UNION ALL
SELECT 82,'key 3',1974,78 UNION ALL
SELECT 83,'key 2',1974,90 UNION ALL
SELECT 84,'key 1',1974,16 UNION ALL
SELECT 85,'key 3',1975,80 UNION ALL
SELECT 86,'key 1',1975,40 UNION ALL
SELECT 87,'key 2',1975,28 UNION ALL
SELECT 88,'key 3',1976,47 UNION ALL
SELECT 89,'key 1',1976,25 UNION ALL
SELECT 90,'key 2',1976,76 UNION ALL
SELECT 91,'key 1',1977,33 UNION ALL
SELECT 92,'key 2',1977,33 UNION ALL
SELECT 93,'key 3',1977,21 UNION ALL
SELECT 94,'key 2',1978,83 UNION ALL
SELECT 95,'key 1',1978,90 UNION ALL
SELECT 96,'key 3',1978,36 UNION ALL
SELECT 97,'key 2',1979,71 UNION ALL
SELECT 98,'key 1',1979,84 UNION ALL
SELECT 99,'key 3',1979,59 UNION ALL
SELECT 100,'key 1',1980,72 UNION ALL
SELECT 101,'key 3',1980,56 UNION ALL
SELECT 102,'key 2',1980,91 UNION ALL
SELECT 103,'key 3',1981,3 UNION ALL
SELECT 104,'key 2',1981,70 UNION ALL
SELECT 105,'key 1',1981,22 UNION ALL
SELECT 106,'key 3',1982,17 UNION ALL
SELECT 107,'key 1',1982,18 UNION ALL
SELECT 108,'key 2',1982,56 UNION ALL
SELECT 109,'key 3',1983,52 UNION ALL
SELECT 110,'key 2',1983,115 UNION ALL
SELECT 111,'key 1',1983,49 UNION ALL
SELECT 112,'key 3',1984,77 UNION ALL
SELECT 113,'key 2',1984,10 UNION ALL
SELECT 114,'key 1',1984,51 UNION ALL
SELECT 115,'key 1',1985,93 UNION ALL
SELECT 116,'key 2',1985,31 UNION ALL
SELECT 117,'key 3',1985,24 UNION ALL
SELECT 118,'key 1',1986,42 UNION ALL
SELECT 119,'key 2',1986,123 UNION ALL
SELECT 120,'key 3',1986,54 UNION ALL
SELECT 121,'key 3',1987,85 UNION ALL
SELECT 122,'key 1',1987,30 UNION ALL
SELECT 123,'key 2',1987,80 UNION ALL
SELECT 124,'key 3',1988,40 UNION ALL
SELECT 125,'key 1',1988,28 UNION ALL
SELECT 126,'key 2',1988,78 UNION ALL
SELECT 127,'key 2',1989,101 UNION ALL
SELECT 128,'key 1',1989,58 UNION ALL
SELECT 129,'key 3',1989,63 UNION ALL
SELECT 130,'key 2',1990,88 UNION ALL
SELECT 131,'key 3',1990,8 UNION ALL
SELECT 132,'key 1',1990,75 UNION ALL
SELECT 133,'key 3',1991,50 UNION ALL
SELECT 134,'key 1',1991,63 UNION ALL
SELECT 135,'key 2',1991,120 UNION ALL
SELECT 136,'key 2',1992,41 UNION ALL
SELECT 137,'key 1',1992,54 UNION ALL
SELECT 138,'key 3',1992,82 UNION ALL
SELECT 139,'key 2',1993,35 UNION ALL
SELECT 140,'key 3',1993,33 UNION ALL
SELECT 141,'key 1',1993,61 UNION ALL
SELECT 142,'key 3',1994,35 UNION ALL
SELECT 143,'key 2',1994,36 UNION ALL
SELECT 144,'key 1',1994,21 UNION ALL
SELECT 145,'key 2',1995,46 UNION ALL
SELECT 146,'key 1',1995,19 UNION ALL
SELECT 147,'key 3',1995,70 UNION ALL
SELECT 148,'key 2',1996,1 UNION ALL
SELECT 149,'key 3',1996,15 UNION ALL
SELECT 150,'key 1',1996,10 UNION ALL
SELECT 151,'key 3',1997,1 UNION ALL
SELECT 152,'key 1',1997,88 UNION ALL
SELECT 153,'key 2',1997,68 UNION ALL
SELECT 154,'key 2',1998,15 UNION ALL
SELECT 155,'key 1',1998,76 UNION ALL
SELECT 156,'key 3',1998,31 UNION ALL
SELECT 157,'key 3',1999,43 UNION ALL
SELECT 158,'key 2',1999,25 UNION ALL
SELECT 159,'key 1',1999,82 UNION ALL
SELECT 160,'key 2',2000,20 UNION ALL
SELECT 161,'key 1',2000,81 UNION ALL
SELECT 162,'key 3',2000,45 UNION ALL
SELECT 163,'key 2',2001,55 UNION ALL
SELECT 164,'key 1',2001,39 UNION ALL
SELECT 165,'key 3',2001,38 UNION ALL
SELECT 166,'key 3',2002,22 UNION ALL
SELECT 167,'key 2',2002,5 UNION ALL
SELECT 168,'key 1',2002,79 UNION ALL
SELECT 169,'key 1',2003,66 UNION ALL
SELECT 170,'key 3',2003,64 UNION ALL
SELECT 171,'key 2',2003,106 UNION ALL
SELECT 172,'key 1',2004,27 UNION ALL
SELECT 173,'key 3',2004,89 UNION ALL
SELECT 174,'key 2',2004,81 UNION ALL
SELECT 175,'key 2',2005,73 UNION ALL
SELECT 176,'key 3',2005,75 UNION ALL
SELECT 177,'key 1',2005,24 UNION ALL
SELECT 178,'key 2',2006,128 UNION ALL
SELECT 179,'key 1',2006,64 UNION ALL
SELECT 180,'key 3',2006,42 UNION ALL
SELECT 181,'key 2',2007,18 UNION ALL
SELECT 182,'key 1',2007,4 UNION ALL
SELECT 183,'key 3',2007,28 UNION ALL
SELECT 184,'key 3',2008,26 UNION ALL
SELECT 185,'key 1',2008,45 UNION ALL
SELECT 186,'key 2',2008,75 UNION ALL
SELECT 187,'key 2',2009,116 UNION ALL
SELECT 188,'key 1',2009,91 UNION ALL
SELECT 189,'key 3',2009,68 UNION ALL
SELECT 190,'key 1',2010,34 UNION ALL
SELECT 191,'key 3',2010,73 UNION ALL
SELECT 192,'key 2',2010,63
I didn't extend this all the way out to the 45th column set but hopefully you get the idea ...
SELECT theKey,
Year1 = MAX(CASE WHEN yearOrder = 1 THEN theYear END),
Value1 = MAX(CASE WHEN yearOrder = 1 THEN theValue END),
Year2 = MAX(CASE WHEN yearOrder = 2 THEN theYear END),
Value2 = MAX(CASE WHEN yearOrder = 2 THEN theValue END),
Year3 = MAX(CASE WHEN yearOrder = 3 THEN theYear END),
Value3 = MAX(CASE WHEN yearOrder = 3 THEN theValue END),
Year4 = MAX(CASE WHEN yearOrder = 4 THEN theYear END),
Value4 = MAX(CASE WHEN yearOrder = 4 THEN theValue END),
Year5 = MAX(CASE WHEN yearOrder = 5 THEN theYear END),
Value5 = MAX(CASE WHEN yearOrder = 5 THEN theValue END),
Year6 = MAX(CASE WHEN yearOrder = 6 THEN theYear END),
Value6 = MAX(CASE WHEN yearOrder = 6 THEN theValue END),
Year7 = MAX(CASE WHEN yearOrder = 7 THEN theYear END),
Value7 = MAX(CASE WHEN yearOrder = 7 THEN theValue END),
Year8 = MAX(CASE WHEN yearOrder = 8 THEN theYear END),
Value8 = MAX(CASE WHEN yearOrder = 8 THEN theValue END),
Year9 = MAX(CASE WHEN yearOrder = 9 THEN theYear END),
Value9 = MAX(CASE WHEN yearOrder = 9 THEN theValue END),
Year10 = MAX(CASE WHEN yearOrder = 10 THEN theYear END),
Value10 = MAX(CASE WHEN yearOrder = 10 THEN theValue END),
Year11 = MAX(CASE WHEN yearOrder = 11 THEN theYear END),
Value11 = MAX(CASE WHEN yearOrder = 11 THEN theValue END),
Year12 = MAX(CASE WHEN yearOrder = 12 THEN theYear END),
Value12 = MAX(CASE WHEN yearOrder = 12 THEN theValue END),
Year13 = MAX(CASE WHEN yearOrder = 13 THEN theYear END),
Value13 = MAX(CASE WHEN yearOrder = 13 THEN theValue END),
Year14 = MAX(CASE WHEN yearOrder = 14 THEN theYear END),
Value14 = MAX(CASE WHEN yearOrder = 14 THEN theValue END),
Year15 = MAX(CASE WHEN yearOrder = 15 THEN theYear END),
Value15 = MAX(CASE WHEN yearOrder = 15 THEN theValue END),
Year16 = MAX(CASE WHEN yearOrder = 16 THEN theYear END),
Value16 = MAX(CASE WHEN yearOrder = 16 THEN theValue END),
Year17 = MAX(CASE WHEN yearOrder = 17 THEN theYear END),
Value17 = MAX(CASE WHEN yearOrder = 17 THEN theValue END),
Year18 = MAX(CASE WHEN yearOrder = 18 THEN theYear END),
Value18 = MAX(CASE WHEN yearOrder = 18 THEN theValue END),
Year19 = MAX(CASE WHEN yearOrder = 19 THEN theYear END),
Value19 = MAX(CASE WHEN yearOrder = 19 THEN theValue END),
Year20 = MAX(CASE WHEN yearOrder = 20 THEN theYear END),
Value20 = MAX(CASE WHEN yearOrder = 20 THEN theValue END),
Year25 = MAX(CASE WHEN yearOrder = 25 THEN theYear END),
Value25 = MAX(CASE WHEN yearOrder = 25 THEN theValue END),
Year30 = MAX(CASE WHEN yearOrder = 30 THEN theYear END),
Value30 = MAX(CASE WHEN yearOrder = 30 THEN theValue END),
Year35 = MAX(CASE WHEN yearOrder = 35 THEN theYear END),
Value35 = MAX(CASE WHEN yearOrder = 35 THEN theValue END),
Year40 = MAX(CASE WHEN yearOrder = 40 THEN theYear END),
Value40 = MAX(CASE WHEN yearOrder = 40 THEN theValue END),
Year45 = MAX(CASE WHEN yearOrder = 45 THEN theYear END),
Value45 = MAX(CASE WHEN yearOrder = 45 THEN theValue END),
Year50 = MAX(CASE WHEN yearOrder = 50 THEN theYear END),
Value50 = MAX(CASE WHEN yearOrder = 50 THEN theValue END)
FROM
(SELECT recID,
theKey,
theYear,
theValue,
ROW_NUMBER() OVER(PARTITION BY theKey ORDER BY theYear) as yearOrder
FROM #TEST) sq
GROUP BY theKey
ORDER BY theKey
You can do the same basic thing with the PIVOT operator, but I don't think it's particularly efficient.
July 29, 2010 at 4:17 pm
One approach is to concatenate Occ, ThisValue, ThatValue, OtherValue into one block and then to dynamically PIVOT the data based on this concatenated block and the rows present. Then in the final SELECT statement, the Occ, ThisValue, ThatValue, OtherValue components can be unwrapped by string manipulation
IF NOT OBJECT_ID('tempdb.dbo.#Policy') IS NULL DROP TABLE #Policy
SELECT 1 AS Occ, 100 AS ThisValue, 250 AS ThatValue, 78 AS OtherValue INTO #Policy
UNION ALL SELECT 2, 200, 60, 150
UNION ALL SELECT 3, 1000, 100, 65
UNION ALL SELECT 4, 355, 625, 99
;
DECLARE @DetailCount INT
DECLARE @SQL NVARCHAR(MAX)
DECLARE @DetailList VARCHAR(MAX)
DECLARE @DetailSplit VARCHAR(MAX)
DECLARE @i INT
DECLARE @ivarchar VARCHAR(10)
SELECT @DetailCount = MAX(Occ) FROM #Policy
SET @DetailList = ''
SET @DetailSplit = ''
SET @i = 1
WHILE @i <= @DetailCount
BEGIN
SET @ivarchar = CONVERT(VARCHAR(10), @i)
SET @DetailList = @DetailList + '[' + @ivarchar + '],'
SET @DetailSplit = @DetailSplit + 'LEFT([' + @ivarchar + '], CHARINDEX(''|'', [' + @ivarchar + ']) - 1) AS Occ' + @ivarchar + ',' + CHAR(13) + CHAR(10) +
'SUBSTRING([' + @ivarchar + '], CHARINDEX(''|'', [' + @ivarchar + ']) + 1, CHARINDEX(''|'', [' + @ivarchar + '], CHARINDEX(''|'', [' + @ivarchar + ']) + 1) - CHARINDEX(''|'', [' + @ivarchar + ']) - 1) AS ThisValue' + @ivarchar + ',' + CHAR(13) + CHAR(10) +
'REVERSE(SUBSTRING(REVERSE([' + @ivarchar + ']), CHARINDEX(''|'', REVERSE([' + @ivarchar + '])) + 1, CHARINDEX(''|'', REVERSE([' + @ivarchar + ']), CHARINDEX(''|'', REVERSE([' + @ivarchar + '])) + 1) - CHARINDEX(''|'', REVERSE([' + @ivarchar + '])) - 1)) AS ThatValue' + @ivarchar + ',' + CHAR(13) + CHAR(10) +
'REVERSE(LEFT(REVERSE([' + @ivarchar + ']), CHARINDEX(''|'', REVERSE([' + @ivarchar + '])) - 1)) AS OtherValue' + @ivarchar + ',' + CHAR(13) + CHAR(10)
SET @i = @i + 1
END
SET @DetailList = LEFT(@DetailList, LEN(@DetailList) - 1)
SET @DetailSplit = LEFT(@DetailSplit, LEN(@DetailSplit) - 3)
SET @SQL =
'
;
WITH
PolicyCombine AS
(
SELECT
Occ,
CONVERT(VARCHAR, Occ) + ''|'' + CONVERT(VARCHAR, ThisValue) + ''|'' + CONVERT(VARCHAR, ThatValue) + ''|'' + CONVERT(VARCHAR, OtherValue) AS Details FROM #Policy
)
,
ctePivot AS
(
SELECT *
FROM PolicyCombine
PIVOT
(
MAX(Details) FOR Occ IN
(
' + @DetailList + '
)
)
AS pvt
)
SELECT
' + @DetailSplit + '
FROM ctePivot
'
EXEC SP_EXECUTESQL @SQL, N'@innerDetailList VARCHAR(MAX), @innerDetailSplit VARCHAR(MAX)', @DetailList, @DetailSplit
July 29, 2010 at 6:46 pm
Steve, I would highly suggest that you read Jeff Moden's article on pivots and crosstabs[/url], especially the section on performance at the end. If you're not only using a PIVOT, but you're also doing string functions and using dynamic sql there is pretty much no way you're going to come close the the performance of a crosstab (as in my example above.) Also, in my opinion, the PIVOT/string function/dynamic sql is going to be harder to maintain as it is harder to read.
The crosstab may look like it's a lot of work because it's more lines of code, but it's just a matter of copy and paste along with cntrl+H in SSMS.
July 30, 2010 at 6:17 am
Thanks, bteraberry! That is orders of magnitude simpler than what I came up with! I still have to tweak it a bit because the criteria changes twice within the 45 rows-into-columns, but this is a great start.
Thanks again!
August 6, 2010 at 9:04 am
I've use the COALESCE function to do what you've done, although I've only had to flatten one column. You may be able to use this. The code sample below is from somewhere on the internet, not my work.
declare @FinalString nvarchar(250)
SET @FinalString=''
SELECT @FinalString = COALESCE(@FinalString + ', ', '') + (CAST([name] AS nvarchar(50)))
FROM Fruit
order by [name]
SELECT REPLACE(@FinalString,'''',' ')
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply