-- CONVERTING ROWS INTO COLUMNS . . .
BEGIN
SET NOCOUNT ON;
IF OBJECT_ID('TEMPDB..#PATIENT_HISTORY') IS NOT NULL DROP TABLE #PATIENT_HISTORY
IF OBJECT_ID('TEMPDB..##RESULT') IS NOT NULL DROP TABLE ##RESULT
DECLARE @PAT_ID NVARCHAR(10)
DECLARE @COLUMN_NAME NVARCHAR(MAX) = ''
DECLARE @COUNTER INTEGER = 1
DECLARE @ROW_COUNT INTEGER = 0
DECLARE @INS_FILED_NAME NVARCHAR(MAX) = ''
DECLARE @INS_FILED_VALUES NVARCHAR(MAX) = ''
-- SAMPLE DATAS, USING THE TABLE OF #PATIENT_HISTORY
CREATE TABLE #PATIENT_HISTORY ( PAT_ID NVARCHAR(10), PAT_NAME NVARCHAR(20), IMAGE NVARCHAR(10) )
INSERT INTO #PATIENT_HISTORY VALUES
('01','ARUNKUMAR',NULL),
('01','ARUNKUMAR','IMAGE2'),
('01','ARUNKUMAR','IMAGE3'),
('01','ARUNKUMAR','IMAGE4'),
('01','ARUNKUMAR','IMAGE5'),
('02','TEX','IMAGE1'),
('02','TEX','IMAGE2'),
('02','TEX','IMAGE3'),
('02','TEX','IMAGE4'),
('02','TEX','IMAGE5'),
('02','TEX','IMAGE6'),
('02','TEX','IMAGE7'),
('02','TEX','IMAGE8'),
('04','SONY','IMAGE1'),
('06','RAKI','IMAGE1'),
('06','RAKI','IMAGE2'),
('06','RAKI','IMAGE3'),
('06','RAKI','IMAGE4'),
('06','RAKI','IMAGE5'),
('06','RAKI','IMAGE6'),
('05','ALPU','IMAGE1'),
('05','ALPU','IMAGE2'),
('03','SAJITH','IMAGE1'),
('03','SAJITH','IMAGE2'),
('03','SAJITH','IMAGE3') -- HERE YOU CAN ADD ROWS WITH COMMAS . . . ( *** SHOULD NOT BE REPEATED *** )
-- HERE TAKING TOP 1 PAT_ID, WHICH ROWS HAVING MAXIMUM ROW COUNT * * *
SET @PAT_ID = ( SELECT TOP 1 PAT_ID
FROM #PATIENT_HISTORY
GROUP BY PAT_ID
ORDER BY COUNT(IMAGE) DESC )
-- TAKING THE ROWS COUNT, RELATED TO THAT @PAT_ID VARIABLE FROM THE #PATIENT_HISTORY TABLE
SET @ROW_COUNT = ( SELECT COUNT(*) FROM #PATIENT_HISTORY WHERE PAT_ID = @PAT_ID)
-- HAVING THAT @ROW_COUNT, DOING THE LOOPING AND TAKING THE DYNAMIC COLUMN NAME AS 1,2,3,4 WITH FIXED VARCHAR(50) WHAT IS THE IMAGE COLUMN TYPE IS ASSIGNED IN #PATIENT_HISTORY TABLE
-- USING @COLUMN_NAME, STORING THE DYNAMIC COULUMNS AND ITS DATATYPE . . .
WHILE(@COUNTER<=@ROW_COUNT)
BEGIN
SET @COLUMN_NAME = @COLUMN_NAME + QUOTENAME(CONVERT(NVARCHAR(10),@COUNTER),']') + ' NVARCHAR(10), '
SET @COUNTER = @COUNTER + 1;
END
-- CREATING THE ##RESULT TABLE HAVING THAT @COLUMN_NAME FIELD . . .
-- HERE PAT_ID , PAT_NAME FIELDS ARE FIXED . . .
EXEC(' CREATE TABLE ##RESULT ( PAT_ID NVARCHAR(10) , PAT_NAME NVARCHAR(20), ' + @COLUMN_NAME + ')')
-- LOOPING METHOD FOR TAKING THE ROW BY ROW PATIENT DETAILS . . .
DECLARE LOOPING CURSOR FOR SELECT DISTINCT PAT_ID FROM #PATIENT_HISTORY
OPEN LOOPING FETCH NEXT FROM LOOPING INTO @PAT_ID
WHILE @@FETCH_STATUS = 0
BEGIN
SET @INS_FILED_NAME = '';
SET @ROW_COUNT = 0;
SET @COUNTER = 1
SET @INS_FILED_VALUES = ''
-- TAKING THE ROW COUNT, WHICH PAT_ID BELONGS TO @PAT_ID VARIABLE IN #PATIENT_HISTORY TABLE . . .
SELECT @ROW_COUNT = COUNT(*) FROM #PATIENT_HISTORY WHERE PAT_ID = @PAT_ID
-- HAVING THAT ROW COUNT GENERATING THE "INSERT INTO FIELD NAMES " . . .
-- USING THE @INS_FILED_NAME VARIABLE TO STORE THE INSERT INTO FIELD NAMES . . .
WHILE (@COUNTER<=@ROW_COUNT)
BEGIN
SET @INS_FILED_NAME = @INS_FILED_NAME + QUOTENAME(CONVERT(NVARCHAR(10),@COUNTER),']')
SET @COUNTER = @COUNTER + 1
SET @INS_FILED_NAME= @INS_FILED_NAME + IIF(@COUNTER<=@ROW_COUNT,',','')
END
-- USING OF STUFF FUNCTION GENERATING THE NO.OF.ROW VALUES TO SINGLE ROW . . .
-- USING THE @INS_FIELD_VALUES VARIABLE STORING THE VALUES . . .
SET @INS_FILED_VALUES =
( SELECT DISTINCT ''''+ PAT_ID + ''', ' + '''' + PAT_NAME + ''', ' + (SELECT STUFF((SELECT DISTINCT ',' + QUOTENAME(ISNULL(IMAGE,'NULL'),'''') + ''
FROM #PATIENT_HISTORY A
WHERE A.PAT_ID = B.PAT_ID
AND A.PAT_ID = @PAT_ID
FOR XML PATH('')), 1, 1, '') ) AS INS_IMAGE_VALUES
FROM #PATIENT_HISTORY B WHERE PAT_ID = @PAT_ID)
-- EXECUTING TO ##RESULT TABLE . . .
EXEC(' INSERT INTO ##RESULT ( PAT_ID, PAT_NAME, ' + @INS_FILED_NAME + ' )' + ' VALUES ( ' + @INS_FILED_VALUES + ' ) ' )
FETCH NEXT FROM LOOPING INTO @PAT_ID
END
CLOSE LOOPING
DEALLOCATE LOOPING
--INPUT
-- SELECT * FROM #PATIENT_HISTORY
-- OUTPUT
SELECT * FROM ##RESULT
END
Converting Rows INTO Columns
Converting Rows INTO Columns